-
1. Re: Calculate rolling LTM using DATEDIFF to work in a table
Jim DehnerFeb 14, 2018 5:13 AM (in response to Anders Fagerholt)
1 of 1 people found this helpfulHi
Not certain what you are doing but see the calculation below for rolling sum
WINDOW_SUM(sum([Sales Target]),-11,0)
this will give you an R12 - you need to put in the limits - your calculation for last month is not correct it should be dateadd('month',-1,[Order Date]) but I don't know where or if you would use it
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
2. Re: Calculate rolling LTM using DATEDIFF to work in a table
Norbert MaijoorFeb 14, 2018 5:24 AM (in response to Anders Fagerholt)
Hi Anders,
Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.
1. "Proof of the Pudding";)
2. Define Custom Date NM D1. Order Date (Month/Year)
3. NM M1. Sales LTM: window_sum(sum([Sales Target]),-11,0)
//calculate the sum over the last 11 month & current month for each month
4. Drag the required objects to the indicated locations
5. NM M1 Sales LTM > Right Click > Text menu > Compute using > NM D1. Order Date (Month/Year)
Regards,Norbert
-
Forum - Rolling LTM_nalmai_10.5.twbx 707.3 KB
-
-
3. Re: Calculate rolling LTM using DATEDIFF to work in a table
Anders Fagerholt Feb 14, 2018 5:29 AM (in response to Jim Dehner)Thanks! Saw this now.
WINDOW_SUM is what I need - not sure how I could miss that.
Thanks!
-
4. Re: Calculate rolling LTM using DATEDIFF to work in a table
Jim DehnerFeb 14, 2018 5:37 AM (in response to Anders Fagerholt)
1 of 1 people found this helpfulAnders
Glad to help out - when you write a calculation the box that opens has a category level - when you open it you get just the formula in that category
when working with table just look at the table calcs and find the one that applies
jim
-
5. Re: Calculate rolling LTM using DATEDIFF to work in a table
Anders Fagerholt Feb 14, 2018 6:22 AM (in response to Jim Dehner)Perfect, thanks again, Jim.
Anders
-
6. Re: Calculate rolling LTM using DATEDIFF to work in a table
Jim DehnerFeb 14, 2018 3:08 PM (in response to Anders Fagerholt)
Glad to help out
Jim