Restrict data for Current month and last two months

[Former Member]

Hi Experts,

I am using BO 4.1 and I am a newbie to IDT.

I need to display Webi report for only three months dynamically. i.e. current month and last two months.

If Current month is October, then the report should display data for August, September, October.

Please note, the data should be restricted automatically and don't want to prompt users to select the month.

I know we can use the functions "month" and "CurDate" for this. Like: month(CurDate)

Please correct me if I am wrong.

Now, these functions didn't work in SQL Assistant/SQL Expression/SQL Definition of new Dimension object. It gives me error and I am not seeing these functions under fx.

Where should I use these functions to filter the data at the Universe or Query level? Kindly provide an example formula/function.

Please help!

Thanks in advance!!

-Jeni

[jeff_crisp · Accepted solution]

If you do not want to prompt the user, then try the following:

DateColumn >= toDate((timestampAdd('SQL_TSI_MONTH', -2, timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now()))))

This will give you everything >= the 1st of 2 months ago.

[jeff_crisp]

I could not reply to your email.  Did you happen to figure out where to place this filter in IDT?

[Former Member]

Try using this -

Date>=

Add_Months(@prompt("report run date"),-3) and

Date<=@prompt("report run date")

Hope this helps.