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

chetan_cricut
chetan_cricut ⚪️
in Ideas Exchange

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
Up
2 votes

Active · Last Updated

Comments

  • MichelleH
    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
    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
    EdgarChieng247 Australia 🟡

    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.

Categories