Hello,
I am trying to calculate Week over Week for the following values.
I’m trying to use the “Period over period computation” however I need a ‘date’ for each of my values to do so. In my case, my data is aggregated by week (202236 for WK36 of 2022…) so I don’t have ‘date format’.
So I am not able to use the Period over period computation without converting my Weeks in dates (first day of each week for example).
Do you know how I could do such thing?
Thank you for your support.
Sarah
Hi SarahV,
If your date field is always in the same format of year + week number, you can convert your date field into a usable date format for the periodOverPeriod function by using the following calculated field. Simply replace “{test date (YYYY + Week #) Ex. 202236}” with your current date field.
addDateTime((parseInt(substring({test date (YYYY + Week #) Ex. 202236},5,2))*7)-7,‘DD’,parseDate(concat(‘01/01/’,substring({test date (YYYY + Week #) Ex. 202236},1,4)), ‘dd/MM/yyyy’))
Please let me know if this resolves your issue. Thanks!
Hello Peter, thank you so much for your support.
Unfortunately, it is not working.
My ‘current data filed’ is “time_week” which is a Number. And ‘substring’ is not accepting such a Number value. Would you know a workaround?
Thank you again.
SarahV.
Hi Sarah,
You can toString() your numeric date field and convert it to string first to use Peters solution.
Alternatively, you can use the Difference function directly with your existing fields. On your table, set the sort on the week column to Ascending order, and create a calculated field similar to below:
WOW% = difference( sum(Values), [time_week ASC], -1, [] )
Difference - Amazon QuickSight.
Let us know if this works?
Thank you very much Asem. Your code is working. It’s resolved.
Have a good day,
SarahV
Hello,
I’m using your method to calculate my WOW%.
Hello team, I am trying to calculate my WOW RAP Variation in bps here.
(sum({yes_responses_received})/sum({total_responses_received})
difference( (sum({yes_responses_received})/sum({total_responses_received}))*10000, [{time_week} ASC], -1, [] )
It works for the previous weeks but not for the current week. The dataset was refreshed on 2022/10/25 (so week43). As you can see on the img, it gives a nonsense number for WK43. It should be +94 bps. This will auto resolve when WK43 will end.
However, I would like to have a correct number here, even for current Week.
Do you know how I can resolve this issue?
Can you also break out the sum of yes_responses_recieved and the sum of total_responses received for the weeks so I can get a better understanding?
