Hi,
I have some data that I aggregated on a monthly level. I want to get a mean and a standard deviation of the 6-rolling month period.
Let’s say we are in August - I want to take the data from Feb to July and calculate its mean and standard deviation.
Thanks in advance!
To get the mean of your aggregation you can take the sum of you aggregation and divide it by the distinct count of months (6).
mean = sumOver(aggregation)/6
To get the standard deviation I think you just need to take the stdevOver.
stdevOver(aggregation)
Let me know if that works
Hi Max,
This is not what I am looking for as I don’t have a fixed number of months. I can have 12 months all together but want to look at a 3-month and a 6-month rolling period. For example, in the example above I want to look at the 6-month trailing period for August so I’ll look at the data from Feb to July. For the 6-month trailing period for July I’d look at the data from Jan to June and so on. Also another example would be to look at different trailing periods so I can look at the n-month period.
Are you looking to divide it just by the amount of months you have filtered? If so you can do something like this.
sumOver(count({session_id}))/avg(distinctCountOver(truncDate(‘MM’,{event_timestamp}),[],PRE_AGG))
This will take the distinct count of months that is selected.
Let me know if that is what you are looking for
Hi, @luce. We hope Max’s solution worked for you. Let us know if this is resolved. (And if it is, we’d love it if you could click/tap “Solution" under his answer.) Thanks! Welcome to the QuickSight Community!
