Skip to main content

Good afternoon dear community. I need to create one chart and I have one event which comes from Segment service. I need to display in the chart the sum of incoming events for the previous 30 days. And also for every day during the past 30 days. For example, today is May 11, I need to display the number of events from May 11 to April 11 - this will be the 1st bar in the chart, then I need to display from May 10 to April 10 - this will be the 2nd bar in the chart and so on until the date interval from April 11 to March 11. That is, there will be 30 bars in the chart with intervals in the previous 30 days. The key is that it would not be necessary to manually select date intervals for 30 days, but that they would be set up automatically (the last 30 days were simply taken). I found that under each event you can add a where "Server upload time" condition. That is, there is a way to add 30 identical events and manually add the where "Server upload time" condition to each of them, but the problem is that the condition will need to be changed every day and this is inconvenient, the conditions should change automatically (the last 30 days).
1) Is it possible somehow in the "where" condition to get the current date and subtract 30 days from it?
2) Is there something similar to the SQL builder in Amplitude (I tried to find it but could not do it, when creating a chart I do not have the "Amplitude SQL" tab) with which you can achieve the desired result?
3) Is there any other way to achieve this?

Thank you for your time, any help would be greatly appreciated!

Hey @sergii 

If I understand your use case correctly, you can use the rolling metric to achieve this. You can either use the Rolling Window metric directly in the event segmentation chart or a custom formula.  Something on this lines - https://analytics.amplitude.com/demo/chart/new/7t5fb55

and https://analytics.amplitude.com/demo/chart/new/ml14pxh.

So each day’s datapoint in here is the rolling sum of the event totals of the previous 30 days.I think only line chart view is supported in the rolling calculations and not a bar chart view.

Afaik, working with the Server Upload timestamp is the way you asked isn’t possible.

 

Let me know if this helps.

 

Hi @Saish Redkar !

Thank you very much for the quick response, this is really what we need! I managed to make one chart. But there was an issue with another chart, where we have to use the calculations in the UNIQUES(A)*100)/UNIQUES(B) formula, which involves 2 events. I tried editing the ROLLWIN() formula in the following way:

ROLLWIN(UNIQUES, (A*100)/B, 7)
ROLLWIN(UNIQUES, (UNIQUES(A)*100)/UNIQUES(B), 7)

But I get an error: Invalid formula

Can you please let me know if there is some way to use the formula to display the Rolling Window values?

Hi @sergii ,

 

I do not believe the syntax of your ROLLWIN is correct. In order to get the correct syntax for this, I would ask that you refer to our custom formulas guide here. Hope that helps!

 

Kind Regards,
Denis

Hi @Denis Holmes !
Yes, the ROLLWIN function is not written correctly. Correct formula syntax:

ROLLWIN(UNIQUES, A, 7)

In my case, I need to put some event calculations into this formula, namely A*100/B, where A and B are different events. And I can't figure out how to correctly write a formula to transfer my calculations

A*100/B instead of A. Are there any solutions for this?

I would be grateful for your answer!

Hi @sergii ,

 

Thanks for the reply. You can use ROLLWIN(UNIQUES, A, 7) or you can use 

ROLLWIN(TOTALS, A, 7) or whichever metric and interval you go with. What would be B in your case then? Can you lay out the calculation you want to use in simple terms and I will try to find the solution for you. 

Hi @Denis Holmes !

I created a chart where I added 2 events:
- A
- B

I want a chart to render the result of a simple mathematical calculation between events:
Event A multiplied by 100 and divided by event B - A*100/B.

And I can't substitute the result of these calculations into your ROLLWIN() formula. I'm trying to understand, is there any solution for this? I hope I made it clear. Maybe @Saish Redkar can give more advice on this or someone else from the tech department?

Hi @sergii ,

 

Thanks for the confirmation! So you want to do ROLLWIN(((TOTALS(A)*100)/TOTALS(B)), A,30); basically? In that case, Amplitude would not support nested calculations in a sense. The syntax for that is strictly ROLLWIN(metric, event, # of five-minute intervals/hours/days/weeks/months). So unfortunately, we can’t support that but I am happy to submit a feature request for you if you would be so kind as to provide me with your Org email and your name in a PM, thank you!

Hey @sergii 

I would go with Denis here since your specific use case isn’t supported in the custom formula yet. From what I understand , ROLLWIN works only a particular event at a time instead of arithmetic on multiple events. You can however use the ROLLWIN output and then apply mathematical operations on  the aggregate if you have that alternate use case.

Hi @Saish Redkar ,
Thank you very much for the tip, I managed to achieve the result!
I used ROLLWIN formula result aggregation in my calculations in the following way:

ROLLWIN(UNIQUES, A, 7)*100/ROLLWIN(UNIQUES, B, 7);

Awesome! Glad that you got the result you were looking for.

Reply


Terms & ConditionsCookie settings