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?

score 10 · Answer 1 · answered Apr 25 '19 at 21:18

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.

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" — Raph, Aug 04 '21 at 12:31

score 1 · Answer 2 · answered Jan 08 '23 at 04:03

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

Dharani Kumar · Answer 3 · 2019-03-12T10:18:58.790

0

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

edited Mar 12 '19 at 10:18

answered Mar 12 '19 at 10:00

Dharani Kumar

24
2

I read that too, but does that imply that there is no way to change it? – Duckslayer Mar 12 '19 at 10:09
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. – Dharani Kumar Mar 12 '19 at 10:13
Hope the above solutions helps for you :) – Dharani Kumar Mar 12 '19 at 10:15
2

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. – Duckslayer Mar 12 '19 at 10:24
1

@Duckslayer were you able to find a solution for this? – Jorjani Jul 01 '20 at 17:38
I think AWS put in a feature request, but said not to hold my breath. – Duckslayer Aug 19 '20 at 18:31

score 0 · Answer 4 · answered Sep 18 '22 at 02:20

0

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

answered Sep 18 '22 at 02:20

Daniel James Canil

306
2
10

Start week on monday in AWS Quicksight

4 Answers4