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

Rai Qaiser Hussain

Hi

I have a requirement to compare one date with the last month's date (needs to add field in RPD)

Example

01-Jan-2019 compare with 01-DEC-2018

22-Jan-2019 compare with 22-Dec-2018

I Can't write 30 days ago due to change in number of days in multiple months

anyone please suggest the solution

regards

Joel

Have you tried something like this:

TIMESTAMPADD(SQL_TSI_MONTH, -1,CURRENT_DATE)

you'll need to replace the CURRENT_DATE with whatever date it is that you're working with.

Rai Qaiser Hussain

Hi Joel

When I tried then facing below error, please guide what is wrong with my function

regards

Rai Qaiser Hussain

Addendum

I want to get Original Cash on previous month same date as current date such as Cash of 22-Dec-2018 and current date is 22-Jan-2019.

Teresa Modesta

Hello sweetie, you don't use AGO like that the syntax is as shown below and notice the SH.TimedDim."Month" that is just the hierarchy 'grain' from your time dimension.

Joel

Then in that case now that your actual requirement is now clear, all you need to do is to use the AGO time function

https://docs.oracle.com/middleware/bi12214/biee/BIEMG/GUID-DB4D9819-BF7D-4750-9C71-DDFA2ACE1CDB.htm#GUID-D474E224-DFBB-4…

You just pass the measure, time dimension hierarchical level (in your case, the month level) and the period (in your case 1 as you're going back 1 month).

Rai Qaiser Hussain

Hello Joel

If I shall use the function as you written (Just for example -----   Ago("Revenue"."F1 Revenue"."Original Cash" , "Revenue"."H0 Time"."Month" , 1) )

then data of previous month will be returned instead of any specific date.

I need data of previous month's specific date

regards

Rai Qaiser Hussain

@Larry's Friday Girl: -

I need data of previous month's date instead of previous month

your function will return the data of previous month for example data of Dec-2018 or Nov-2018 but I need data of 01-Dec-2018 or 02-Dec-2018

Teresa Modesta

This is the part where you use your initiative and change the "Revenue"."H0 Time"."Month" to "Revenue"."H0 Time"."Detail"  - note I cannot see your revenue time hierarchy, but you need to reference the hierarchy level that corresponds to day...

If there is not such a level in your time hierarchy that corresponds to dates (day) then you cannot use AGO for this and you might want to have a look at using FILTER instead which is on the function syntax palette.

Rai Qaiser Hussain

Hi Larray

As you can see, I have already used the same method in one of my another function but this method returns the data of 1 day previous,

I need data of previous month's same date not 30 or 31 back date

Gianni Ceresa

Rai Qaiser Hussain wrote:I need data of previous month's specific date

Your logic is flawed and you didn't provide the real requirement covering these flaws:

What is your "previous month's specific date" when you are the 29th or 30th of March? The 29.02 exists (almost) every 4 years, so you could be lucky, but the 30.02 for sure will never exist.

Same thing apply to the 31.7 and any other date with the same behaviour.

So what's your real business logic here?