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

  • 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!**
  • thank you it works

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

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