For example, compare the variation in basis points
Hello @medpere, I think I need a little more information to understand what you are wanting to accomplish here. Are you wanting a WoW calculation to handle different metrics? If that is the case, you will need to make different calculated fields for each metric you want to check.
Please explain a little more about what your data is and what exactly you want to check WoW for and I can try to assist you further.
Hello @DylanM thank you for your time, I want to create a calculated field to show the weekly variation, I already have a calculated field with percentages, so I want the variation between mi current week and the last one. I need to show that metric in basis points.
Hello @medpere, okay great, thank you for the clarification! In order to determine how you would calculate the WoW, how are you currently building the calculated field for the percentage. I will say, ideally these aggregations are more easily built when checking WoW for a metric that is not already being aggregated within QuickSight. Let me know how you are creating the percentages and we can go from there. If we run into any issues, a good alternative would be to create your percentage column in a custom SQL statement, then we are able to utilize periodOverPeriod calculations in QuickSight.
Hello @DylanM ,this is the calculation that I use in order to get the percentage of OTA( On Time Arrival). 1-(distinct_count({Late Arrival VRID})/distinct_count(vrid)). Late Arrivals among total Runs, I use “distinct_count” because I have to discriminate some Runs.
1 Like
Hello @medpere, a function you can try to use to accomplish this would be the Lag function.
It will allow you to bring in values from a previous week into a new column on the current week. For the percentage, you will likely need to bring in both distinct count aggregations individually and then try to get the difference between last week and this week’s aggregations. Let me know how this works, you may run into some syntax errors when trying to calculate the difference between the 2 values.
Hello @medpere, did the function that I suggested above help guide you towards your expected output? If so, please mark it as a solution, otherwise let me know if you have any follow-up questions so I can guide you further. Thank you!
Sorry for the delay @DylanM , I am triying to resolve this.
W6 OTA was 94.57% vs. W5 94.57% the variation WoW will be 0.85% or 85 bps.
I used this funtion:
lag(distinct_count({Late Arrival VRID})/ distinct_count(vrid),[{cpt_week} DESC],1)
I got this result:
100% - W6 95.37% = 4.63%.
I think I am close, but I still do not have what I need.
1 Like
Hello @medpere, I am curious, in your formula that you posted above, you set lag to partition by cpt_week DESC, but you table is showing dates sorted in ascending order. Also, just to clarify, are you running the calculation that creates the percent value in the lag function?
Hello @DylanM , I have tried to use both forms (ascending and descending).
This is the function I use to get the OTA percentage:
1-(distinct_count({Late Arrival VRID})/distinct_count(vrid)).
1- LA / RUNS = 0.9830 in percentage 98.2%
So, I tried this function in order to calculate the difference or the variation WoW.
lag(distinct_count({Late Arrival VRID})/ distinct_count(vrid),[{cpt_week} DESC],1)
I have been reading and there is a possibility to use the function “periodoverperiod” but I do not know how to use it.
1 Like
Hello @medpere, are you able to alter the dataset with custom SQL? If you can bring in the OTA % value from your dataset, and not create the value in a calculated field, this function will be significantly easier. Then you could use periodOverPeriodPercentDifference or periodOverPeriodDifference without running into an aggregation errors. I think if your dataset remains as is, and you are building that percent calculation in a calculated field, you are going to run into aggregation errors.
1 Like
Hi @medpere,
Seems like you were able to resolve your issue. I will mark this as resolved. Please let us know if you still needed more assistance instead.
Thank you,
Asem.
1 Like