How to display week end date instead of week start date when aggregrating daily sales data to weekly

EM
EM
edited March 13 in Datasets

Hi,

 

I've got daily sales data that is currently aggregated in a WoW view in a bar graph in Domo - but the date on the x-axis shows the week start date. I'm trying to find a way to show the week end date on the x-axis instead in the same format ex: Current x-axis is displaying 2020-08-16 and I just need it to show as 2020-08-22 instead. I've tried various Beast Mode formulas/calculations based on my knowledge of SQL and so far, no luck. Any help would be appreciated!

 

Thanks,

E. 

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @EM 

    You can utilize DAYOFWEEK and some simple math to get the last day of the week.

     

     

    `Date` + INTERVAL (7 - DAYOFWEEK(`Date`)) DAY

     

     

    The DAYOFWEEK function returns a number between 1 (Sunday) and 7 (Saturday). Subtracting that from 7 gives us the number of days until the end of the week which we just add that number of days to the exiting `Date` value.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • German_Rodriguez
    German_Rodriguez Member

    thank you it works

    `startDate`+ INTERVAL (7 - DAYOFWEEK(`startDate`)) DAY

    Start date: 12/4/2022 // Week end Date: 12/10/2022

  • john_nguyen
    john_nguyen Member

    i, could someone please tell me what is wrong with my case statement below? It validates without any errors, but when I try to load it, I receive the following error: "An issue has occurred during processing. We are unable to complete the request at this time."

    I am trying to create a calculated field with the following case statement in order to improve filtering on my report, but I keep encountering the aforementioned error.

    Any assistance would be greatly appreciated.

    Thank you

    CASE
    WHEN JS Date Added + INTERVAL (7 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (6 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (5 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (4 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (3 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (2 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (1 - DAYOFWEEK(JS Date Added)) then 1
    WHEN JS Date Added + INTERVAL (0 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-6 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-5 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-4 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-3 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-2 - DAYOFWEEK(JS Date Added)) then 0
    WHEN JS Date Added + INTERVAL (-1 - DAYOFWEEK(JS Date Added)) then 0
    else 3
    END

  • marcel_luthi
    marcel_luthi Contributor

    @john_nguyen what are you trying to achieve here? The formula is failing because you're not specifying what the INTERVAL is (DAY, MONTH, etc), once you add that before the THEN on each clause it will no longer fail. However, it's most likely not doing what you want as the WHEN statement is not really returning a logical/boolean true or false, what is the objective of the Beast Mode, in which scenarios would you expect 0 to be returned vs. those where 1 should be returned?

Categories