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

chetan_cricut
chetan_cricut Member
edited December 2023 in Analyzer Ideas

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 🤔

2
2 votes

Active · Last Updated

Comments

  • @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".

  • 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)

  • 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.