Hi @wtkim
Not sure how the last field is calculated last_week_sales_amount, but if you just want to amount from 7 days ago for each date 7, you can use the lag function like this:
lag
(
sum(Amount),
[Date ASC],
7
)
it assumes that no dates are missing. If you only want one day, say Monday to get to a different day in time, you can use the lag function with an ifelse statement.
But if you want the WEEK period to start on a SATURDAY and then do WEEK OF WEEK calculations using the new week definition, then here is a possible solution. Try it and see if it works:
To account for multiple years, when the first day of the year may or may not be a Friday, first we need to find the first FRIDAY in the entire dataset. In Quicksight Sunday is the first day, 1, so Saturday would be 7.
Create a calculated field called ‘First Saturday’ like this:
ifelse(
extract(‘WD’, minOver(Date,,PRE_AGG)) = 7, minOver(Date,,PRE_AGG),
extract(‘WD’, minOver(Date,,PRE_AGG)) = 1, addDateTime(6, ‘DD’, minOver(Date,,PRE_AGG)),
extract(‘WD’, minOver(Date,,PRE_AGG)) = 2, addDateTime(5, ‘DD’, minOver(Date,,PRE_AGG)),
extract(‘WD’, minOver(Date,,PRE_AGG)) = 3, addDateTime(4, ‘DD’, minOver(Date,,PRE_AGG)),
extract(‘WD’, minOver(Date,,PRE_AGG)) = 4, addDateTime(3, ‘DD’, minOver(Date,,PRE_AGG)),
extract(‘WD’, minOver(Date,,PRE_AGG)) = 5, addDateTime(2, ‘DD’, minOver(Date,,PRE_AGG)),
NULL)
Then we calculate the number of days in between this first Saturday and any other date in the dataset. We will then divide that number by 7 and keep only the integer without the remainder. This will be the week number from the first Saturday for any given date. Create a calculated field called ‘Weeks from first Saturday’:
floor( dateDiff(FirstSaturday,Date,‘DD’) / 7 )
Then create a calculated field to get the total Amount by the Week that now starts with Saturday. Call it ‘New Weekly Amount’
sum(Amount,[{Weeks from first Saturday}])
Add it to the visual. The apply a table calculation and select Difference.
For ‘calculate as’ choose ‘Table down across’.
Then you should end up with something like this, where for every day, you can see the week (now starting with Saturday) and the difference between the currrent week and the previous week (starting with Saturday).
Make sure you edit the table and change the name of the last column to reflect its logic (WoW difference)
If it solves your question, please mark it as a solution.
Thanks,
GL
