How to compare today's date with last month's date in OBIEE

Gianni Ceresa

Rai Qaiser Hussain wrote:Yes you are right, If such exception raise then I shall return the zero amount.There are two phases of this requirementPhase 1, fullfil the above requirementPhase 2, Add exception for such scenarios

Why 2 phases when you can solve it in once? Let's forget phases (if the business gave you the phases on top of the weird requirement they could directly do the job).

Because the logic you look for is "special" (or "weird" as other replies pointed out), trying to solve it by formula will result in a bunch of logic which needs to be executed every time somebody open that analysis.

I would solve it in the database by adding to your time dimension a new column pointing to the date you want following your logic: the ETL can easily generate it and that logic in SQL which you execute once and populate the column is quick and simple.

Once you have the column you make a join in the RPD and create that measure as logical column thank to the new column you use in the join. Your need to have no amount (or zero) when there isn't the same date the month before (29-31.2, 31.4 etc.) can be achieved by a NULL or any other default value in the column resulting in a missing join. If you make it an outer join you get a NULL back and can do what you want with it.

Here again I see an issue: you want zero, but 0 is a value and is totally different than NULL which itself is different than no value at all. You definitely need to challenge these things. Your whole requirement is "weird" and in just few minutes the person asking for it will probably change his mind if you start challenging things.

[Deleted User]

0 isn't NULL isn't '' isn't '0' isn't ' ' and so on and so on

Rai Qaiser Hussain

Yes but in my case I will use 0 instead of NULL