Leap Year Fix

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...

Find more posts tagged with

Comments

ST_Superman

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

ST_Superman

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

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()