Hey @sergii
If you are just looking for the aggregate result over the last 7 days, then the custom formula
PROPSUM(A)/TOTALS(B) over a Bar Chart view can help, as seen in this demo chart.
PROPSUM(A) should sum the property value over the last 7 days in aggregate ( in your case the “spend” property)
Let me know if this fits logically for your use case.
Hi @Saish Redkar , thanks for your answer,
This is not exactly what I meant. I mean using nested functions. I have to select 180 days, not 7 in the chart itself: link and use
(PROPSUM(A) for the last 7 days/ TOTALS(B) for the last 7 days)
calculations in the formula itself.
So, the chart will render the last 180 days and each dot on it will be a sum of certain data for the last 7 days.
I tried using the ROLLWIN() function, but I am not sure it works for my use case.
Plus, the error appears if using it this way:
ROLLWIN(TOTALS, PROPSUM(A), 7)/ROLLWIN(TOTALS, B, 7)
How can I rewrite it to have the needed result?
You can try out - ROLLWIN(PROPSUM, A, 7)/ROLLWIN(TOTALS, B, 7)
As seen here - https://analytics.amplitude.com/demo/chart/new/pzrvxg2
Hi @Saish Redkar , thank you for your reply!
It truly works with the ROLLWIN() formula if we need to get an average moving value for the last 7 days.
But I need to get a sum of values for the last 7 days within 180 days as a result. I found the CUMSUM() formula which does almost the same. But I cannot set up a number of days for which I need to have the sum of all properties. Logically, the formula should look this way:
CUMSUM(PROPSUM, A, 7)/CUMSUM(UNIQUES, B)
180 days should be selected in the chart itself: screenshot. but I am getting 400 status code error. I understand that the CUMSUM() does not support 3rd parameter (7 days). But maybe there's another way to achieve the needed result? I would appreciate to have your thoughts!
Hey @sergii
CUMSUM returns the metric for selected event with a running total of days/weeks/months over the chart's timeframe. As you rightly said, cumsum doesn’t support a 3rd parameter and it will take the time period from the chart interval as the expected behavior.
So CUMSUM(PROPSUM, A, 7) will error out.
Per your requirement “the chart will render the last 180 days and each dot on it will be a sum of certain data for the last 7 days”, the ROLLWIN formula should do this since ROLLWIN sums the data over the time frame ( 7 days). This differs from the rolling average, in that a rolling window does not average your data over the selected time frame
So ROLLWIN(PROPSUM, A, 7) over the 180 day chart interval should work in this case, unless I have missed some interpretation here.
