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

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,

Find more posts tagged with

Comments

GrantSmith

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.

German_Rodriguez

thank you it works

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

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

jp2631

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

@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?