calculating WTD, YTD and QTD with rules

Post Reply
Rtel
Posts: 32
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

calculating WTD, YTD and QTD with rules

Post by Rtel » Thu Apr 11, 2019 9:35 pm

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

User avatar
jim wood
Site Admin
Posts: 3682
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: calculating WTD, YTD and QTD with rules

Post by jim wood » Fri Apr 12, 2019 11:23 am

Moved to the correct forum.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

tomok
MVP
Posts: 2538
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

Post by tomok » Fri Apr 12, 2019 12:50 pm

Rtel wrote:
Thu Apr 11, 2019 9:35 pm
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lotsaram
MVP
Posts: 3220
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

Post by lotsaram » Fri Apr 12, 2019 3:16 pm

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.
I second that.
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.

CellPutN
Posts: 18
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

Post by CellPutN » Fri Apr 12, 2019 5:33 pm

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

User avatar
gtonkin
MVP
Posts: 653
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: calculating WTD, YTD and QTD with rules

Post by gtonkin » Fri Apr 12, 2019 6:48 pm

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

Post Reply