Solved

Custom formula (Sum of event A properties "spend" for the last 7 days)/(Sum of events B for last 7 days)

  • 3 August 2022
  • 5 replies
  • 608 views

Userlevel 1
Badge

Greetings,
I faced an issue with rendering a chart using your custom formulas.
In general 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 perform the following calculations:
1) Take the "spend" property value (can be any number) and sum them up over the last 7 days.
2) Divide the result by the sum of Event B calls over the last 7 days.

If I try to imagine the formula logically, in a simple way, then it should be something like:
(Sum of event A properties "spend" for the last 7 days)/(Sum of events B for last 7 days)

I checked your documentation together with tech support topics, but I couldn't find the way to create such a formula.

Please let me know if anything's unclear. I hope you can help me with achieving it.

icon

Best answer by Saish Redkar 3 August 2022, 21:11

View original

5 replies

Userlevel 7
Badge +10

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.
Userlevel 1
Badge

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?

Userlevel 7
Badge +10

You can try out - ROLLWIN(PROPSUM, A, 7)/ROLLWIN(TOTALS, B, 7)
As seen here - https://analytics.amplitude.com/demo/chart/new/pzrvxg2

Userlevel 1
Badge

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!

Userlevel 7
Badge +10

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.

Reply