Hello! I would like to calculate a movingsum formula where the starting month is always fixed (eg Jun 18) but the last month is always the current month. So for Mar 22 I would like the cumulative sum from Jun 18 to Mar 22 but for Apr 22 I would like the sum from Jun 18 to Apr 22. How can I do this?
Solved
Moving sum fixed start date, dynamic end date
+1- Wallflower
Best answer by Nathan
Sorry I wrote it incorrectly:
cumulate(metric[filter:Month.'Start Date'>= DATE(2028,01,01)],Month)
In terms of dimensions, all you need is for your source metric to have the Month dimension
+9- Master Author
Does your data start on Jun 18?
You can use the CUMULATE function to keep a rolling tab of the total
+1- Wallflower
My data start on Jan 15 but maybe I could create a metric to make zero all data before Jun 18 so that CUMULATE function is based on this one. However even when I try to do CUMULATE (metric 1, metric 2) where metric 1 is the free cash flow & metric 2 the month, from month 1 it brings a huge amount. When trying to put the BY dimension it produces an error. Is there any advice when using this formula how we should be handling dimensions?
+12- Employee
you could try like this:
cumulate(metric[filter:Month.Start Date >= DATE(2028,01,01),Month]
ps: would be better to put the date in a metric itself
+12- Employee
try to do this formula in the playground so you’ll see the dimensions being computed
+1- Wallflower
Hi
+12- Employee
- Answer
Sorry I wrote it incorrectly:
cumulate(metric[filter:Month.'Start Date'>= DATE(2028,01,01)],Month)
In terms of dimensions, all you need is for your source metric to have the Month dimension
Log into the Pigment Community
No account yet? Create an account
Single Sign-On Need help?
Log in with your Pigment accountEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.