Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: srinu_anu2007 on 04 Aug 2014 04:22:37 AM

Title: Previous Year and Previous Month
Post by: srinu_anu2007 on 04 Aug 2014 04:22:37 AM
how to show previous year and previous month when you are selecting Current month and current year.

ex:i am selecting current month:Aug and Current year:2014 and i need to show Jul 2013.

I have Month level hierachy in my cube and MUN for Aug is: [rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q3 2014^Aug 2014]. Can someone help me.

Thanks
Title: Re: Previous Year and Previous Month
Post by: MFGF on 04 Aug 2014 04:25:00 AM
Quote from: srinu_anu2007 on 04 Aug 2014 04:22:37 AM
how to show previous year and previous month when you are selecting Current month and current year.

ex:i am selecting current month:Aug and Current year:2014 and i need to show Jul 2013.

I have Month level hierachy in my cube and MUN for Aug is: [rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q3 2014^Aug 2014]. Can someone help me.

Thanks

The month before the one selected can be targeted using the prevMember() function

eg prevMember([rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q3 2014^Aug 2014])

The same applies for members at the Year level.

Cheers!

MF.
Title: Re: Previous Year and Previous Month
Post by: srinu_anu2007 on 04 Aug 2014 06:43:40 AM
Hi MF,

Thanks your reply. I tried the same function and its retreving July 2014 but i need to show July 2013.

Thanks,
Title: Re: Previous Year and Previous Month
Post by: MFGF on 04 Aug 2014 06:50:26 AM
Quote from: srinu_anu2007 on 04 Aug 2014 06:43:40 AM
Hi MF,

Thanks your reply. I tried the same function and its retreving July 2014 but i need to show July 2013.

Thanks,

In that case, you need to use the parallelPeriod() function around all of this

eg

parallelPeriod([Your year level], 1, <the expression you are using above>)

This assumes that the year level is somewhere above the month level in your time hierarchy.

MF.
Title: Re: Previous Year and Previous Month
Post by: srinu_anu2007 on 04 Aug 2014 07:34:11 AM
below expression is not giving any value.

parallelPeriod ([rp_gcrs].[rp_period].[rp_period].[Year and Variances],1,prevMember([rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q3 2014^Aug 2014]))
Title: Re: Previous Year and Previous Month
Post by: MFGF on 04 Aug 2014 07:40:42 AM
Quote from: srinu_anu2007 on 04 Aug 2014 07:34:11 AM
below expression is not giving any value.

parallelPeriod ([rp_gcrs].[rp_period].[rp_period].[Year and Variances],1,prevMember([rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q3 2014^Aug 2014]))

Well, I don't have access to your member tree to see whether the member you are trying to target actually exists...

MF.
Text only | Text with Images