Week of starting on Monday

s_f_OTT
s_f_OTT Member
in Cards, Dashboards, Stories

I am having trouble with a "Week of" beast mode. Domo has the ability built into the cards to group the data by week. It does not, however, allow you to pick the start of the week. Our client wants the week to start Monday instead of Sunday. I have been trying to figure how to accomplish this. The below code works for all days except Saturday. For example, see the uploaded screenshot. The "week of" is set to Monday for all the dates except for Saturday. Saturday week of is Saturday. I am not following why this is happening but any help would be greatly appreciated.

DATE_SUB(Date, INTERVAL
CASE
WHEN WEEKDAY(Date) = 0 THEN 5 -- Saturday
WHEN WEEKDAY(Date) = 1 THEN 6 -- Sunday
WHEN WEEKDAY(Date) = 2 THEN 0 -- Monday
WHEN WEEKDAY(Date) = 3 THEN 1 -- Tuesday
WHEN WEEKDAY(Date) = 4 THEN 2 -- Wednesday
WHEN WEEKDAY(Date) = 5 THEN 3 -- Thursday
WHEN WEEKDAY(Date) = 6 THEN 4 -- Friday
END
DAY)

image.png

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @s_f_OTT If the value of your date field is on a Saturday, then the WEEKDAY function will return 7, not 0. So if you change the 0 in the first condition of your case statement it will apply the correct number of days to subtract.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @s_f_OTT If the value of your date field is on a Saturday, then the WEEKDAY function will return 7, not 0. So if you change the 0 in the first condition of your case statement it will apply the correct number of days to subtract.

Categories