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
calculating WTD, YTD and QTD with rules
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: calculating WTD, YTD and QTD with rules
Moved to the correct forum.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: calculating WTD, YTD and QTD with rules
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.
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: calculating WTD, YTD and QTD with rules
I second that.tomok wrote: ↑Fri Apr 12, 2019 12:50 pm 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.
And I'm pretty sure @David Usherwood would provide a 3rd
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 22
- Joined: Mon Oct 01, 2018 1:50 pm
- OLAP Product: TM1
- Version: 10.2.20500.75
- Excel Version: 2016
- Location: Montreal, Canada
Re: calculating WTD, YTD and QTD with rules
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
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
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: calculating WTD, YTD and QTD with rules
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
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