Getting Current YTD and Rolling 12 Months From Current Month Member

[cognos05]

Hi,

I have my Current FYTD and Rolling 12 Months in different Hierarchy under Time Dimension generated by Transformer.

For a specific report the user requesting to view the report by both FYtd and Rolling 12 Months.

So I decided to use a static prompt with two values and use the code

?prmMonth? = 'A'
Then (CurrentFYTD)
Else
(ROlling 12 Months)

Now since these members are from different Hierarchy cognos will throw an error.


SO now I have a Months Hierarchy which has all the months and I can get the current Month using the MUN
'[DistributorSalesCube].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'

so from here I need to create a member for  FYTD and Rolling 12 Months
And in Slicer it expects a value and I cant use a set.

So how to achieve this and is this a right approach .

Thanks,

[cognos05]

Guys ,
Any suggestion on this , I need to form a single member for rolling 12 months from current month .IBM suggests to use two cross tabs one for rolling 12 months and other for current fytd and use conditional formatting and show/ hide on selection.

My fiscal months hierarchy has years,months...so I can use 2016 to get the current fytd, but for rolling months ,I am able to use lastperiods(12,2015oct) but this is a set and my slicer filter will throw error.

thanks,
Nithya

[Francis aka khayman]

ibm suggestion sounds good. use two crosstabs in a conditional block. the crosstab displayed is determined by static promt.

[cognos05]

But I have to use 2 cross tabs and this requirement is used in few report pages, so means more crosstabs .so I thought I would some how calculate Rolling 12 Months from current Month and use it in the slicer as a member.

Is at all there is a way to get this differently.. I can think of using lag(currentmonth,1)+lag(currentmonth,2)+,,lag(currentmonth,12) and use that..but looking for a better approach.

Thanks,
Nithya

[Francis aka khayman]

good luck  :D

[MFGF]

But I have to use 2 cross tabs and this requirement is used in few report pages, so means more crosstabs .so I thought I would some how calculate Rolling 12 Months from current Month and use it in the slicer as a member.

Is at all there is a way to get this differently.. I can think of using lag(currentmonth,1)+lag(currentmonth,2)+,,lag(currentmonth,12) and use that..but looking for a better approach.

Thanks,
Nithya

The alternative approach is to not use the relative time hierarchies and to use the main time hierarchy utilising some dimensional expressions to deliver Current YTD and Rolling 12 Months.

Current YTD could be derived using periodsToDate() eg

periodsToDate([your Year level],[current Month member])

Rolling 12 months could be derived using lastPeriods() eg

lastPeriods(12,[current Month member])

MF.

[cognos05]

MFGF,

Yes I used the same lastPeriods(12,[current Month member]) for Rolling 12 Months from the Main time Hierarchy, but on slice if I use
? promptMonth? ='Rolling 12'
Then( last periods (12, CurrenMonth) will not work as it is a set, its expecting one member .

Thanks,
Nithya

[MFGF]

MFGF,

Yes I used the same lastPeriods(12,[current Month member]) for Rolling 12 Months from the Main time Hierarchy, but on slice if I use
? promptMonth? ='Rolling 12'
Then( last periods (12, CurrenMonth) will not work as it is a set, its expecting one member .

Thanks,
Nithya

Hi

? promptMonth? ='Rolling 12'

This looks like a detail filter expression. Can you explain?

MF.

[cognos05]

Yes,

I have a  cross tab where in I am showing my Distributors or Accounts in rows and in columns I have grouped several products ,Now I have my measure as H0 Sales  and I am also showing population for the accounts in the column along with the products.

HoSales           Pd1        Pd2        pd3     Population
Account 1       
Account 2


Now I have to filter this report on two time periods,  that is on Rolling 12 Months and Current YTD

When you see my attached screenshot, my time dim will have rolling 12 months and cyrrent YTD in different hierarchy.


so here I will have to add a static propmt with these values and on user selection from value prompt, I have to filter or slice my data according to the time period
my slicer

will be like   if ?prmptMonthValue ? = 'Cytd'
                   then (Currentytd)
                  Else(Rolling12 Months)
Now for Rolling 12 Months I cant use lastperiods(12, currentMonth) since its a set, I need a member to use it .


Thanks,
Nithya