How to create weekly YoY metric in AWS QuickSight?

user_1324765 0
Viewed 2927

I am trying to calculate a sum of a column for a specific calendar week, and want to compare that to the same week in the previous year.

So let's say I have the sum of WK21 for 2020 is 1000 and sum of WK21 in 2019 is 800. The field should return: 200.

Attached is how my dataset looks like.

enter image description here

I would like to sum the credit per WK for each segment, and see how the difference is to the sum of that week for that same segment, in the previous year. Later on I would change the difference to percentDifference, but I assume the main formula is the same.

Is that easy doable?

Tried this (percentDifference) for WoW (in the same year), and it worked using this formula:

percentDifference(sum({credit}),[{wk} ASC],-1,[{year}, industry, segment])

But when trying for weekly YoY the following formula, it didnt work (-52 because 52 weeks in year): percentDifference(sum({credit}),[{wk} ASC, year ASC],-52,[industry, segment])

PS: for the country, I didnt take that in consideration, as I want to filter for the countries I want later on...

amazon-quicksight data-visualization dashboard
2 Answers

For Year-on-year difference create a calculated field like the following

difference(sum({credit}), [{wk} ASC, {year} ASC], -1, [{Industry}, {Segment}])

For year-on-year percentage difference your calculated field would be

percentDifference(sum({credit}), [{wk} ASC, {year} ASC], -1, [{Industry}, {Segment}])

This is because the lookup index in the function (-1) is based on the sort of the rows (sort order in the square brackets in the function)

Source: percentDifference and difference QuickSight Docs

user_152441510

Try -100 instead of -52. Since the 'wk' field is not a date-defined field, it is probably just considering 'wk' as an int.

user_64471040
Related