TM1 Forum

Hi
I have a cube with time dimension and in measure dim i have probability element. But theoretically probability of quarter 1 is not equal to sum of probability of jan, feb, mar. It is average of jan,feb,mar. TM1 always aggregates values as sum. How do i change it to average. I have time dim with hierarchy TotalYears->(years from 1990-2011)->Quarters->Month. Same type of aggregation(Avg) must reflect to all consolidations. How can i do that. Please help me.

Thanks
Daya

Do you have one time dimension with hierarchy like this?

2010
-2010Q1
--201001
--201002
--201003
-2010Q2
--201004
--201005
--201006
...

Thanks for the reply
hierarchy is shown in the attachment.

i dono if ur able to c the attachment...ill type in
its like

Total year
1990
1990-1Q
January 1990
February 1990
March 1990
1990-2Q
April 1990
- -
--
--
--
--
2010
2010-1Q
January 2010
February 2010
March 2010
2010-2Q
April 2010
--
--
--
--
--

Hi,

One working approach is using alternative hierarchies to support the calculation.

1. Create an alternative hierarchy like this in which you will always sum-up the values on C level elements
2010-Sum
2010-1Q-Sum
January 2010
February 2010
March 2010
2010-2Q-Sum
April 2010

2. Create rule to calculate the average for the original C level elements
['Total year']=0; (optional)

['Probability']=C:
IF(LONG(!timedimensionname)=4,DB(cubename,.....,timedimensionname|'-Sum',.....)/12,
IF(LONG(!timedimensionname)=7,DB(cubename,.....,timedimensionname|'-Sum',.....)/3,STET));

HTH

PS: sometimes (especially in reporting cubes) you have to solve other issues with averages when you have unfinished quarters or years
in which not all months has value and therefore seeing the values of Q-Sum/3 or Y-Sum/12 is not meaningful for the end users.

Kind Regards,
Peter

Sorry, "!" is required in the DB() function in front of your time dimension name.

['Probability']=C:
IF(LONG(!timedimensionname)=4,DB(cubename,.....,!timedimensionname|'-Sum',.....)/12,
IF(LONG(!timedimensionname)=7,DB(cubename,.....,!timedimensionname|'-Sum',.....)/3,STET));

Thank you so much for ur reply.
But the problem is..not all months has data in it...cuz of this we get a wrong calculation if we divide by 12 or 3 for year/Quarter.
Consider 2010, it has data upto june. How can we solve such a problem.
Please help.

daya007 wrote:Thank you so much for ur reply.
But the problem is..not all months has data in it...cuz of this we get a wrong calculation if we divide by 12 or 3 for year/Quarter.
Consider 2010, it has data upto june. How can we solve such a problem.
Please help.

It depends on what your users would like to see and how they would like to control the valid periods.
E.g. you can create a lookup cube to flag the valid periods by 1 and change the calculation a bit:

['Probability']=C:
IF(LONG(!timedimensionname)=4,DB(cubename,.....,!timedimensionname|'-Sum',.....)/DB('lookupcubename',!timedimensionname,'ValidPeriods'),
IF(LONG(!timedimensionname)=7,DB(cubename,.....,!timedimensionname|'-Sum',.....)/DB('lookupcubename',!timedimensionname,'ValidPeriods'),STET));

The flags can be updated manually or in a TI process after uploading the actuals into your report cube.

Use \ instead of /
['Probability']=C:
IF(LONG(!timedimensionname)=4,DB(cubename,.....,!timedimensionname|'-Sum',.....)\DB('lookupcubename',!timedimensionname,'ValidPeriods'),
IF(LONG(!timedimensionname)=7,DB(cubename,.....,!timedimensionname|'-Sum',.....)\DB('lookupcubename',!timedimensionname,'ValidPeriods'),STET));

There are a few other general ideas for averages in this post: http://forums.olapforums.com/viewtopic.php?f=21&t=388 I think Method 2 will probably work pretty well for you.

Martin