Solved

Custom formula CUMSUM(PROPSUM, A, 7)/CUMSUM(UNIQUES, B)

  • 9 August 2022
  • 2 replies
  • 515 views
S
Rank
Userlevel 1
Badge

Greetings,
The ticket is related to the previous ticket: link, which you marked as SOLVED without responding there. Though I never mentioned the issue is resolved, please kindly help me further:

Let me describe the issue again:
In genera,l I have two events:
- event A
- event B
1) Event A has the "spend" property, I will have to use this property value in the formula.
2) In Event B I would need to use the number of unique UNIQUES(B) calls - this is clear.

I need to have a sum of values of event A for the last 7 days within 180 days. I found the CUMSUM() formula which does almost everything I need, but I cannot set up a number of days for which I want to get a sum of values. Logically, the formula should look like this:


CUMSUM(PROPSUM, A, 7)/CUMSUM(UNIQUES, B)

I need to select 180 days on the chart: https://prnt.sc/oKAwqbOI5rmI.
But I am getting 400 error: status code. I understand that the CUMSUM() formula does not support the third parameter (7 days). But maybe there's another way to achieve the result?

Please share more information @Saish Redkar 
Thank you

icon

Best answer by Saish Redkar 9 August 2022, 17:36

View original

2 replies

Saish Redkar
Rank
Userlevel 7
Badge +10

Hey @sergii 

Reposting the reply from here -

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.

Saish | Need additional Amplitude help ? Book me on topmate.io/saish_redkar
S
Rank
Userlevel 1
Badge

Hi @Saish Redkar , thanks for your reply!


You're right, ROLLWIN() should really be good for my case. But, for some unknown reason, the formula calculates with a small error.
To test this, I did the following:

1) For example, I have an event B and I simply render the number of completed events for the last 30 days using the UNIQUES(B) formula, then I get the following data: https://i.imgur.com/DYO5bBr.png.
If you add these events together for the last 7 days, the result will be = 959. (145+153+152+116+149+147+97)

2) Next, if I create a separate chart with the same event B using the  ROLLWIN(UNIQUES, B, 7) formula for the same last 30 days, I get the result - 955 for August 16, (https://i.imgur.com/ b7RO1me.png), but it should be 959.

That is, a discrepancy of 4 units is obtained. For different days, the discrepancy is different, but minor. Could you please explain to me where the discrepancy comes from and how to get rid of it. I will be grateful for your answer!

Reply


Terms & ConditionsCookie settings