calculating WTD, YTD and QTD with rules

[Rtel]

Hello,I am looking to calculate WTD, MTD and YTD for sales for a business. The cube will have standard Dim such as Dept, Acct etc.
Time dimension is at week level and all the sales data are provided at week level.

WTD is calculated as last week sale
MTD is calculated as sum of all the weeks beginning from the month to the week of last Saturday
QTD is calculated as sum of all the weeks beginning from the first week of current Quarter to the week of last Saturday

What is the best way to calculate this in TM1 cube.

Thanks in advance for all the help and suggestions.

Rtel

[jim wood]

Moved to the correct forum.

[tomok]

What is the best way to calculate this in TM1 cube.

Here's your simple answer. Don't. Time period calculations should almost always be done with a hierarchy in the Time dimension and letting the consolidation feature in TM1 take care of it. It will perform light years faster than a rule and you won't have to worry about feeders.

[lotsaram]

Here's your simple answer. Don't. Time period calculations should almost always be done with a hierarchy in the Time dimension and letting the consolidation feature in TM1 take care of it. It will perform light years faster than a rule and you won't have to worry about feeders.

I second that.
And I'm pretty sure @David Usherwood would provide a 3rd

[CellPutN]

Hi,

I suggest you to take a look at this white paper.

https://static1.squarespace.com/static/ ... Design.pdf

There's an entire section on how to structure time dimensions.

Cheers,
Max

[gtonkin]

I will 3rd that in David's absence.

I had a sales performance model at daily level using all sorts of rules to do the same "rollups".
Feeding was an absolute nightmare as a sale on 1 Jan had to effectively feed into the rest of the year to ensure that WTD, MTD, YTD were adequately fed.

Short of it is that after a few years the models was awfully slow to open views and consumed almost 75Gb of RAM.

I then got the client to agree on a re-write using a single period dimension with the required rollups - keep in mind that WTD becomes tricky as for sales models you want day 1 in week 1 in year 1 which may be a Monday to correlate with the same day next year otherwise you end up comparing Sundays to Mondays - obviously depends on your requirement however.

Exported all the data, built the Period dimension and rebuilt the cube.
Memory usage down to under 5Gb and views opened in a snap and still do 3 years down the track and at roughly 7Gb memory usage (mostly taken up by the input data as this is for a retail bank with 700+ branches, 10000+ staff who could make sales)

The other thing to play around with after building your time dimension is your TM1s.cfg parameter for ViewConsolidationOptimizationMethod