We need to calculate a week over week % change, but we want to compare the current week numbers with average of past three weeks.Is there a function that can help achieve this.
Example week 1 = 34
week 2 = 30
week 3= 36
week 4 = 45
Calculation needed is % change between 45 and average of (34+30+36)
Hey there!
I think I’ve figured it out. Let me know if this works for you. I am using the count of sessions, but substitute your measure in.
First let’s make a calculation to get the avg of your calculations based on the weeks.
avgOverSession= count({session_id})/distinct_count(truncDate(‘WK’,{event_timestamp}))
Then I am going to split your timestamp field into two categories. Whether it is this week or the last three weeks. For this, I am truncating to the week. Feel free to play with taking the truncDate off if the calculation is not fully correct.
is last three weeks = ifelse(dateDiff(truncDate(‘WK’,{event_timestamp}),truncDate(‘WK’,now()),‘WK’)<=3 AND dateDiff(truncDate(‘WK’,{event_timestamp}),truncDate(‘WK’,now()),‘WK’)>0,‘Last Three Weeks’,dateDiff(truncDate(‘WK’,{event_timestamp}),truncDate(‘WK’,now()),‘WK’)=0,‘This Week’,NULL)
Then you can take the percent difference of the avgOversession and order it by is last three weeks.
percent difference= percentDifference(avgOverSession,[{is last three weeks} ASC],-1)
I’ve put it in a table here. For this table I filtered out any event that was not part of the last three weeks or this week.
If you want it in an insight you can make a top ranked calculation and display that number. I only took the number of results to be one and again filtered only to the last four weeks.
Here is what it would look like
Let me know if that works, or if I missed anything
