Start week on monday in AWS Quicksight

Question

I know I can change the date field granularity to week in AWS Quicksight, and I can also display the date by week number. But as far as I understand, Quicksight defines the start of a week on Sunday, and I need it to be Monday.

Is there any way to start the week on Monday in AWS Quicksight?

Agiotage

Answer 1

Here is a formula for a calculated field you can add that will group your dates by weeks starting on Mondays. You should be able just replace {date_date} with your field name and copy/paste this into the formula box (including the newlines) and it will do the trick.

addDateTime(
    1, 
    'DD', 
    truncDate(
        'WK',
        ifelse(
            extract(
                'WD',
                {date_date}
            ) = 1, 
            addDateTime(
                -1, 
                'DD', 
                {date_date}
            ), 
            {date_date}
        )   
    )
)

This field will equal the Monday that starts the week your date falls into.

You can put this all in one line but I added the line breaks for readability.

It essentials checks if the date is on a Sunday, and moves it to the previous week and then just adds a day to the normal trunc function so that the weeks begin on Monday.

Debit

Just a quick update following my exchange with the AWS support team "Checking internally, I would like to bring to your notice that a feature request has been submitted to the internal team for QuickSight requesting this functionality. Seeing how this feature could be very useful, I'm glad to inform you that using this case as a reference, I went ahead and added your voice on the existing feature request on your behalf. Rest assured, the QuickSight team are working on this request and it is in pipeline currently. [...] Unfortunately, I cannot confirm if/when the feature will be available" – Lightfoot

Answer 2

As a I workaround for this, I would recommend defining a column (either in the dataset or as a calculated field) that represents the year_week_iso (ie. 2023-W01 or 202301 if you prefer to have an integer value).

This column can then be used to group data in order to show trends over time when the week begins on a Monday.

The integer version of this column can also be leveraged to show the most current week of data by setting up a Top 1 filter that is based off the max(year_week_iso_integer) → example Amazon Quicksight filter to get current week

Fraxinella

Answer 3

AWS Quicksights has finally released custom week day start feature.

How to Use the Custom Week Start Feature Getting started with the Custom Week Start feature is straightforward:

Open your analysis in QuickSight. Navigate to the Edit file menu pane. In the Analysis Settings section under Date and Time, choose the Custom week start day that you want. Select Apply.

https://community.amazonquicksight.com/t/quicksights-custom-week-start-day-feature-and-a-custom-script-for-analysis-week-start-day-or-time-zone-updates/25510#how-to-use-the-custom-week-start-feature-3

Ammonify

Answer 4

Acoording to the documentation https://docs.aws.amazon.com/quicksight/latest/user/truncDate-function.html

WK: This returns the week portion of the date. The week starts on Sunday in Amazon QuickSight.

you can use filters to start like here

Bendwise

I read that too, but does that imply that there is no way to change it? – Agiotage

You can add filter for the date and choose time range as the filter type. Now choose start date and end date. it will start from your start date. – Bendwise

Hope the above solutions helps for you :) – Bendwise

I need to see trends over several weeks, where a week is monday-sunday. Is that doable with filters? As in, the timespan would be a year or more, but aggregated to weeks. – Agiotage

@Agiotage were you able to find a solution for this? – Olenolin

I think AWS put in a feature request, but said not to hold my breath. – Agiotage

Answer 5

This is still an issue when using filters and rolling dates as weeks in analytics.

There is an open thread in the AWS community forum.

https://community.amazonquicksight.com/t/starting-weeks-on-monday-show-gaps-on-custom-weekly-groupings/4222

Ammonify