How to calculate week numbers based on custom week start day

hamza_123
hamza_123 Contributor
in Magic ETL

Question Summary: I want to calculate the week number from a date based on a custom week start day. How can I do this, either in Beast Mode or SQL data transforms. 

 

Explanation:

 

My company Domo settings have Weeks defined from Sunday to Saturday. However, one of my internal client team records weeks from Friday to Thursday. I don't want to change my overall Domo week settings. 

 

Consequently, when I make a card in Domo for my above-mentioned client team and group it by weeks (in date filter, or via presets in Domo calendar filter), wrong weeks are given (from Sunday to Saturday).

 

I've realized on of the solution can be to make a custom column/field using MySQL/Best-mode in my dataset that calculates week numbers based on start day of Friday. e.g. for 2020, 1st and 2nd Jan was Wed and Thursday, so those days would be in Week-1, 2020. After that, it would be Week 2, 2020 and so on. I would then concatenate the weeks and years to make a new columns, and use these instead of dates to represent time 

 

I don't know as of now how to do this calculation. Can anyone help me?

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @hamza_123 

     

    Have you thought about using a beast mode shifting the dates either ahead 2 days or back 5 days (depending on how you want to calculate the week number) and then graphing by week using the new shifted date?

     

     

    DATE_ADD(`Date`, 2)
    DATE_ADD(`Date`, -5)

     

    If you have your Domo settings to be displaying the week with the date this won't work as the date would be wrong (offset) but if you're displaying the week numbers themselves it should work.

     

    If that doesn't get you what you need then you'd have to write a more complex beast mode to calculate the week number.

     

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

    Tricky! But dangerous if the changes are not transparent.

     

    Consider using the fiscal calendar  .https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Using_A_Fiscal_Calendar

     

    Keep in mind, using a fiscal calendar DOES NOT change how DATE functions in Beast modes evaluate.  It only implacts the Date filters in cards.

     

    @GrantSmith wrote:

    Hi @hamza_123 

     

    Have you thought about using a beast mode shifting the dates either ahead 2 days or back 5 days (depending on how you want to calculate the week number) and then graphing by week using the new shifted date?

     

     

    DATE_ADD(`Date`, 2)
    DATE_ADD(`Date`, -5)

     

    If you have your Domo settings to be displaying the week with the date this won't work as the date would be wrong (offset) but if you're displaying the week numbers themselves it should work.

     

    If that doesn't get you what you need then you'd have to write a more complex beast mode to calculate the week number.

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • hamza_123
    hamza_123 Contributor

    thank you for the replies.

     

    @jaeW_at_Onyx   if I get it correctly, a fiscal calendar option can be set up in the backend that will have seperate calendar options (e.g. week start days and year start/end) than the overall company settings? And this fiscal calendar can be applied individually to cards. 

     

    The link you provided was a little vague so just clarifying

Categories