tuple of last 12 months with a seperate Year and Month Dimensions

[sudeepa]

HI All,
i created a report from a TM1 Cube. cube has separate year and Month dimensions. report shows data for last 12 months from selected year and Month. for this i calculated Year with value prompt and Prior Year with if then else statement and Month YTG and Month YTD . cross tab has multiple measures in rows and Prior Year , Year and Children (Month YTG) nested under Prior Year and Children (YTD) nested under Year  as columns. Total of these measures are calculated as Tuple (CurrentMeasure,Prior Year, Month YTG) + Tuple (Currentmeasure, Year, Month YTD). this is working fine for measures where we have to show Totals. but it is not working for some measures. as an example i have attached a screen shot below, where treatments per day Total should show as Total/12 but it is giving as total (prior yr)/count of months in YTG + Total(Year)/Count of months in (Month YTD). can any one help me how to change Total expression so that i can get tuple of measure based on leaf level column (Months)

[MFGF]

...for this i calculated Year with value prompt and Prior Year with if then else statement and Month YTG and Month YTD...

Hi,

Can you share with us how you did these calculations? What expressions have you used? I'd expect the issues are arising as a result of the way you are calculating things, but unless you tell us how you are doing things currently it's impossible to say for sure.

Cheers!

MF.

[sudeepa]

Thanks for the reply. below is the report data items i have calculated:;
1) Year -  #prompt('year','token','[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2013]')#

2) Prior Year - if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2014]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2013]) else
if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2015]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2014]) else
if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2016]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2015]) else
([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2016])

3) Month YTD - if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November YTD]) else  ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[December YTD])

4) Month YTG - if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October YTG]) else
([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November YTG])

5 ) Children of YTD  -- (children([Month YTD]))

and multiple measure in rows .

[sudeepa]

And i am calculating Total as Tuple (Currenemeasure, Prior Year, MonthYTG) + Tuple(Currentmeasure, year, Month YTD)

[sudeepa]

Hi MFMF,
i am still looking for a way to solve this. Do you have any suggestions that will work on report level?

[MFGF]

Hi,

Thanks for the reply. below is the report data items i have calculated:;

1) Year -  #prompt('year','token','[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2013]')#

Ok - so a nice simple prompt macro to return a year member. No problem here.

2) Prior Year - if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2014]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2013]) else
if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2015]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2014]) else
if (?year?='[PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2016]') then ([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2015]) else
([PNL Statistic].[Year].[Year]->:[TM].[Year].[Year].[@MEMBER].[2016])

Yuk! This is a horrible way of doing things. Why not use a nice simple dimensional function for this, eg

prevMember([your Year item above])

or if you prefer

lag([your Year item above],1)

3) Month YTD - if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October YTD]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November YTD]) else  ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[December YTD])

Double yuk! :)
What is ?month? - you haven't told us how you are using it. Assuming it's a prompted member in use in the report, you could change the value prompt for it to define static values (January, February, March etc) then modify your expression for Month to be

#'[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[' + prompt('month','token','January') + ']'#

You could then define Month YTD to be

#'[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[' + prompt('month','token','January') + ' YTD]'#

4) Month YTG - if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[January YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[February YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[March YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[April YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[May YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[June YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[July YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[August YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[September YTG]) else
if (?month?='[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October]') then ([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[October YTG]) else
([PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[November YTG])

Double Double yuk! :)

How about

#'[PNL Statistic].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[' + prompt('month','token','January') + ' YTG]'#

5 ) Children of YTD  -- (children([Month YTD]))
and multiple measure in rows .

This one looks ok

You could perhaps change your total calculation to be

aggregate(currentMeasure within set [your Children of Month YTG item]) + aggregate(currentMeasure within set [your children of Month YTD item])

Cheers!

MF.