How to make date field of week starting on saturday

Hi. I ask a question because I need help :sob:
Sales data is collected 2 days late from the time of inquiry.
There is a problem that all sales data of last week are not collected when trying to compare sales data of last week with sales data of 2 weeks ago every Monday.
I want to use the sales data aggregated from Saturday to Friday as the one-week sales data.
How do I create a calculated field where the week starts on Saturday?
I searched countless times through Quicksight Community and Google but couldn’t find an answer.

Best Regards.

In the screenshot below, you can see that 2023.04.15 (Sat) sales data is not 100% collected.

Have you looked at this?

You would just need to change the logic to be Saturday. Is that what you’re looking for?

1 Like

Hi Max~
yes I already read it. I tried to apply it by referring to the function, but it didn’t work as I wanted.
Thanks for reply! :+1:

What is your expected value for 2023.04.15?

1 Like

Hi @David_Wong ~
I expect… similar sales to last Sunday.
What I want is to make 2023.04.08 to 2023.04.15 last week.
Because every Monday, 100% of sales data from the previous Sunday are not collected.

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:

[Date ASC],

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:

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)),

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.



Hi @wtkim, @David_Wong @Max @gillepa We are currently support custom week start as an out-of-box feature. Please try and let us know if any feedback. Thanks!