sum the value by week and display first/last day of week instead of week no. on x axis

chetan_cricut · 2022-12-15T17:23:05+00:00

There was an error rendering this rich post.

Problem:

Currently when we select a time range on the card by last x weeks, we will see the weekly aggregated values displayed by Week 1, Week 2, Week 3, etc on the x axis

This poses a challenge to rethink on the actual dates when looking at the cards

Proposed Solution:

To display First/last day of week as a date instead of Week 1 for ease of understanding.

PS: I would be hapy to know if there is already a work around for the same 🤔

Find more posts tagged with

Submitted and awaiting review from product team

Status: Submitted and awaiting review from product team

Comments

MichelleH

@chetan_cricut There is a work-around for this using the Calendar report in the Domo Dimensions connector. You can use a dataflow to find the min/max date for each week of the year and then join by date to whatever dataset you're using. Then you can use the min/max date on the x-axis of your card and change the Graph by settings from "By Week" to "None".

chetan_cricut

MichelleH Thanks for the response. I think a beast mode like below is able to solve the problem, along with the options you have suggested.

DATE_ADD(`date_id`, INTERVAL (8- DAYOFWEEK(`date_id`)) DAY)

EdgarChieng247

I use the following for our use case. We report on week ending Friday, meaning our reporting week starts on Sat and ends on a Fri and the date for the week is the Friday. This is the beastmode I use -

--------

case

when DAYOFWEEK(`Created`) = 7 then ((`Created`)+6)

when DAYOFWEEK(`Created`) = 1 then ((`Created`)+5)

when DAYOFWEEK(`Created`) = 2 then ((`Created`)+4)

when DAYOFWEEK(`Created`) = 3 then ((`Created`)+3)

when DAYOFWEEK(`Created`) = 4 then ((`Created`)+2)

when DAYOFWEEK(`Created`) = 5 then ((`Created`)+1)

when DAYOFWEEK(`Created`) = 6 then (`Created`)

end

-------

Domo's default week starts on the Sunday, so Sunday is Day of Week 1, so in my use case I need to roll the day of the week to the following Friday as we report on week ending Friday. So in the case statement above for example, I am saying if the day of week is 1 (which is Sunday), add 5 more days to that date, so it becomes the following Friday date. You do the same for every other day except for Fri for me which is Week Day 6 then it is as is.

If you want to report on Week Starting date, then instead of addition, you'd want to subtract it to the starting day of your week.