BeastMode Help: Days Remaining in Quarter

swagner
swagner Contributor

I've got a beastmode I am trying to create that will calculate the days remaining in a quarter. 

 

  • If the quarter is already over I want to show a value of 0 (not a negative number).  
  • If the quarter hasn't started I want to show 91 (that's the number of days in a quarter)

Any help would be appreciated.  Also if this is better handled in ETL I can go that way as well.

 

This is the concept I am working with (not currently working, and does not include the):

 

 

Case 

when

DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)>91 then 91

when

DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)<0 then 0

else

DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)
end

 

 

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    If I were you, I would create a Date Dimension that has one row per day, and includes, 'End Of Quarter' as a date.  Then JOIN that dataset to your transactions using a FUSION.

     

    Then in Beast mode you can calculate datediff(ActivityDate, EndofQuarter)  You could wrap all that in a CASE statement to calculate if Activity Date is less than Start of Current Quarter ( 

      SELECT  MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER 
    - INTERVAL 1 QUARTER

    )

     

    https://stackoverflow.com/questions/11884618/how-do-i-get-the-first-date-of-a-quarter-in-mysql

     

    Hope that helps!

    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"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    If I were you, I would create a Date Dimension that has one row per day, and includes, 'End Of Quarter' as a date.  Then JOIN that dataset to your transactions using a FUSION.

     

    Then in Beast mode you can calculate datediff(ActivityDate, EndofQuarter)  You could wrap all that in a CASE statement to calculate if Activity Date is less than Start of Current Quarter ( 

      SELECT  MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER 
    - INTERVAL 1 QUARTER

    )

     

    https://stackoverflow.com/questions/11884618/how-do-i-get-the-first-date-of-a-quarter-in-mysql

     

    Hope that helps!

    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"
  • swagner
    swagner Contributor

    Thanks @jaeW_at_Onyx - YOU ROCK!