truncDate behavior

[aheczhen]

Hi all,

I stumbled upon something weird while using the truncDate function.

To add come context, I’m designing dashboards with some level of interactivity, just the basic stuff, date ranges, basic filtering, PoP calculations.

I followed some of the amazing stuff in this article and managed to make almost all of it to work.

The issue I’m having is when using the truncDate function to truncate at the week level. I’m getting the data for the previous period, instead of the period for which start date and end date are set. This question is about this same issue.

Calculated field is:

ifelse(
    ${PeriodTypeParam}='Day',truncDate("DD",{status_created_ts}),
    ${PeriodTypeParam}='Week',truncDate("WK",{status_created_ts}),
    ${PeriodTypeParam}='Month',truncDate("MM",{status_created_ts}),
    ${PeriodTypeParam}='Quarter',truncDate("Q",{status_created_ts}),
    truncDate("YYYY",{status_created_ts})
)

With the above I get:

Visual on the right is the data that should be shown, visual on the left is what I get.

I tried for the Month and Year period type and they both work fine.

I guess a workaround would be:

ifelse(
    ${PeriodTypeParam}='Day',truncDate("DD",{status_created_ts}),
    ${PeriodTypeParam}='Week',addDateTime(1, 'WK', truncDate("WK",{status_created_ts})),
    ${PeriodTypeParam}='Month',truncDate("MM",{status_created_ts}),
    ${PeriodTypeParam}='Quarter',truncDate("Q",{status_created_ts}),
    truncDate("YYYY",{status_created_ts})
)

Any insights and/or assistance into this will be appreciated.

Thanks,

[Xclipse]

Hi @aheczhen

Welcome to the QuickSight community!

The issue you’re facing with truncDate("WK", {date_field}) returning the previous period is likely due to default week truncation behavior, which follows Sunday as the start of the week rather than your expected week start day.

If you expect the week to start on Monday instead of Sunday, try the following calculation. This shifts all dates by +1 day beforehand, effectively aligning the start of the week to Monday.

Your calculation is correct, but there’s a small mistake - you’re adding a week instead of days.

Example: Replace the fields from your dataset

ifelse(
    ${PeriodTypeParam}='Day', truncDate("DD", OrderDate),
    ${PeriodTypeParam}='Week', addDateTime(-mod(extract("WD", OrderDate) - 2, 7),"DD", OrderDate),
    ${PeriodTypeParam}='Month', truncDate("MM", OrderDate),
    ${PeriodTypeParam}='Quarter', truncDate("Q", OrderDate),
    truncDate("YYYY", OrderDate)
)

or

ifelse(
    ${PeriodTypeParam}='Day',truncDate("DD",OrderDate),
    ${PeriodTypeParam}='Week',addDateTime(1, 'DD', truncDate("WK",OrderDate)),
    ${PeriodTypeParam}='Month',truncDate("MM",OrderDate),
    ${PeriodTypeParam}='Quarter',truncDate("Q",OrderDate),
    truncDate("YYYY",OrderDate)
)

[prantika_sinha]

Adding to what @Xclipse suggested, you can also change analysis settings to configure the start of the week.

[aheczhen]

@Xclipse Thanks for the input and the correction.

I haven’t changed the Start of the Week on the analysis settings, as @prantika_sinha suggested, but when I do, I still need to use the updated calculated field to get the correct data, regardless of the start of the week being Sunday or Monday.

My team is already used to select the start date on a Sunday and the end date on a Saturday for the metrics of the data for that specific week. I guess what I’m still failing to grasp is why truncDate would return the data for the previous period.

Thanks for the help!