If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

2 Months ago date filter

Started by Taboka, 25 Jan 2008 05:39:25 AM

Previous topic - Next topic

Taboka

Hi all,
I am using the filter below to get values for 2 months ago:
[Recharge Monthly].[Time Month].[Time Month Key]=(if( substring(to_char(?FromDate?),5,2) = 01)
then ((?FromDate? - 89)-1)
else if( substring(to_char(?FromDate?),5,2) = 02)
then (?FromDate? - 89)
else (?FromDate? - 2))

It works for the instance where the month selected is January,i.e I get results for the previous Dec.However when I test it on the instance where the month selected is Feb(where I'm supposed to get results for the previous Dec),it doesn't return any results.
Could someone maybe help me figure that one out.

Thanks\Regards

Taboka

Taboka

Hi everyone,
I realised the problem was my math in calculating the previous Dec from a selected month of Feb.The correct filter is therefore
[Recharge Monthly].[Time Month].[Time Month Key]=(if( substring(to_char(?FromDate?),5,2) = 01)
then ((?FromDate? - 89)-1)
else if( substring(to_char(?FromDate?),5,2) = 02)
then (?FromDate? - 90)
else (?FromDate? - 2))
and it works like a charm.

Hope this helps someone.

Cheers
Taboka

nishu85

you could have used add_months([date item] , -2 ) for that

COGNOiSe administrator

Add_months and other add_* functions are great indeed.