Calculating Last 90 day average.

[ravi.ahuja21]

Hello Folks,

I am trying to do a calculation where I have to show the user the last 90 day sales average from each day.

Below is the given output that i am expecting (For easy understanding purpose I have taken total of last 3 days for each day).

"Date", "Sales", " Last 3days Running total", "From-Todate"
1-Jan 100 100 (30dec-1jan)
2-Jan 200 300 31 dec-2jan
3-Jan 100 400 1jan-3jan
4-Jan 50 350 2jan-4jan
5-Jan 20 170 3jan-5jan
6-Jan 15 85 4jan-6jan
7-Jan 20 55 5jan-7jan
8-Jan 30 65 6jan-8jan
9-Jan 100 150 7jan-9jan
10-Jan 20 150 8jan-10jan


I am out of ideas as to how should this be calculated in TM1 it is quite easy to do in SQL query and simply populate the cube, however I do not have one dimension I have around 11 dimensions across which I need to take care of this calculation which should be done at runtime. I have already created a similar kind of a calculation in SSAS cube using MDX query, however not really getting any starting to point to write MDX query for calculating a measure value (perhaps "calculating a measure value using MDX" I dont think it is possible in TM1).



Here is the ssas code that I have used, it may not be useful but, I am not just able to absorb the fact that a thing which is possible in SSAS is not possible in TM1.

Code: Select all

Sum(
(
BottomCount(DESCENDANTS([As of Date].[Year -  Quarter -  Month -  Date].CURRENTMEMBER, [As of Date].[Year -  Quarter -  Month -  Date].[Date]),1).item(0).lag(89):
BottomCount(DESCENDANTS([As of Date].[Year -  Quarter -  Month -  Date].CURRENTMEMBER, [As of Date].[Year -  Quarter -  Month -  Date].[Date]),1).item(0)
),
[Measures].[GrossRevenue]
    ) / 90

Request the TM1 gurus to throw light on this topic.

[declanr]

My preferred method would be to TI the average in; you just need to loop through the last 90 days (Alan has a very good topic on here about how to use dates and times), use CellGetN to add the numbers up and then when you hit 90 you could divide by 90... or also do a count while you are at it and divide by the number of populated cells... then do a CellPutN.


You could also do it by rules but that would involve creating a lot of consolidations and would probably be more work/upkeep than the TI way.

Or failing that if the data is loaded from a database elsewhere just calc the average in SQL and pop that straight in.

[tomok]

I'll echo the sentiment that this would be better done while loading the cube and not be "at runtime" as the OP suggests. If you absolutely insist on doing it in rules then the best way is going to be to create a series of consolidations that add up the individual 90 days in question and then divide the current day with the 90 day running consolidation that corresponds to it. In theory, that would mean 365 different 90 day consolidation nodes but you probably only want business days, not weekends and holidays, so that cuts your list down but it's still a lot.

You can name your 90 day consolidation nodes so that they can be identified by the day (and use that logic in your rule, or you can create an attribute to associate the periods and use that. Assuming your measure dimension had elements for Sales and Average_Sales, and you use logic in the naming (calling the consolidation nodes 90_Day_Sales_Ending_Day1, 90_Day_Sales_Ending_Day2, etc.), the rule might look like:

Code: Select all

['Average_Sales'] = N:DB(CubeName, !Dim1, !Dim2, !Dim3,...!Dimx,!Time)\DB(CubeName, !Dim1, !Dim2, !Dim3,...!Dimx,'90_Day_Sales_Ending'|!Time);

[Duncan P]

You can even put the division by 90 in as a weight of 0.01111111111111111 on each element.

[tomok]

You can even put the division by 90 in as a weight of 0.01111111111111111 on each element.

That's actually a way, way better way to do it, performance-wise. I use weights when calculating, monthly, quarterly, and YTD average balances for banking apps because it performs so much better. The only problem with that is you have to create different nodes for leap years.

[ravi.ahuja21]

Thanks for the valuable suggestion, I will try it out and keep you guys posted.

Thanks

[BariAbdul]

Thanks for the valuable suggestion, I will try it out and keep you guys posted.

Thanks

Hi Ravi,have you been able to find the solution for this requirement,If you did could you please post it.Thanks