Leap Year Fix

RG_CBC
RG_CBC NC ⚪️

I noticed that our DOMO was pulling different trend %'s as compared to our regular route accounting software and in digging, I found it to be due to this year being a leap year.

 

When we first launched DOMO, the developer put in a beastmode to use as a slicer so that it would look at sales through the last full business day and compare it to the same date the prior year and we just named it "Future/Historic" and it defaulted to "Historic"  so it matched our regular accounting software:

 

CASE  WHEN (DAYOFYEAR(`date`) < DAYOFYEAR(CURRENT_DATE()))  THEN 'Historic' ELSE 'Future' END 

 

This worked fine until leap year date (February 29th) occured, then it began showing sales trends inaccurately when selected (Example: comparing 3/1/2020 - 3/25/2020 to 3/1/2019 - 3/26/2019). In order to have it compare {3/1/2020 - 3/25/2020} to {3/1/2019 - 3/25/2019}, I made a simple case statement adjustment - shown below:

 

CASE WHEN YEAR(`date`) = 2020 THEN

(CASE  WHEN (DAYOFYEAR(`date`) < DAYOFYEAR(CURRENT_DATE()))  THEN 'Historic' ELSE 'Future' END )

WHEN YEAR(`date`) = 2019 THEN

(CASE  WHEN (DAYOFYEAR(`date`) + 1 < DAYOFYEAR(CURRENT_DATE()))  THEN 'Historic' ELSE 'Future' END )

ELSE 0 END

 

I hope this helps someone else that may have a similar issue to deal with. I'm sure there are other ways to fix this issue, but this was a quick fix that worked...

Comments

  • If you are just looking to denote if it is historical...

     

    Why not just use 

    CASE WHEN `date` < CURRENT_DATE() then 'Historic' ELSE 'Future' End

    **** I just read the question a in a little more detail. 

     

    Your solution is only going to work if you are already past leapday. and only for 2020.  I would try to use something more dynamic.  Let me see if I have time to post a solution later today 

  • I think this will get to what you are looking for:

    case
    when month(`Date of Invoice`)<MONTH(CURDATE()) then 'Historic'
    when month(`Date of Invoice`)>MONTH(CURDATE()) then 'Future'
    when day(`Date of Invoice`)<DAY(CURDATE()) then 'Historic'
    else 'Future'
    end

    In this statement, if the month is less than the current month then it is historic.  If the month is greater, then it is future.

     

    If it is equal to the current month, then it looks at the day in the month to determine.  If the day is less than the current day, then historic.  Otherwise, future.

     

     

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    careful of just considering the MONTH of the date.  You'll combine years in cross year analysis.

    add YEAR() comparison to the beast mode.

     

    to combine year and month i do year()*100+month()