YOY % Calculation not working correctly

Reply
White Belt

YOY % Calculation not working correctly

Hey fellow Dojo users, i am using hte beast  mode calculation below to show YoY % change for our website visits, the chart works fine as long as the timeline is a full year but as soon as we use a filter to narrow down to last 3 months or last 6 months all the values go to zero.  Can anyone help...

 

(
(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE()) THEN `Visits` ELSE 0 END)
- SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE()) - 1 THEN `Visits` ELSE 0 END)
)
/ NULLIF(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE()) - 1 THEN `Visits` ELSE 0 END), 0)
)

Innovation Council jmo
Innovation Council

Re: YOY % Calculation not working correctly

The calculation you have is correct. However, when you filter down to anything less than the last 24 months, the query powering your card will not return data for last year. The statement in the denominator of your calculation will return zero which means you get a division by zero error and no data in the card.

 

It would be best to build a custom filter if you want to look back 3 or 6 months. The filter (or Beast Mode) would return data from the last three months for both this year and last year.

-----
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"
White Belt

Re: YOY % Calculation not working correctly

Ok thank you for the advice, i figured that might be the problem. do you have any samples or sugestions on how to create the custom filters for date ranges less than a full year

Moderator

Re: YOY % Calculation not working correctly

Hi all,

 

Can anybody help @chockley with their follow-up question?

 

Thanks!

Green Belt

Re: YOY % Calculation not working correctly

If you use the new card builder, and drag the field for the dates that you want to use for the filter into the filter area, you should be able to see a variety of options for the custom filter.  Have you been able to do this yet?

"When I have money, I buy books. When I have no money, I buy food." - Erasmus of Rotterdam
Moderator

Re: YOY % Calculation not working correctly

@chockley, did nalbright's response help address your follow-up question?

White Belt

Re: YOY % Calculation not working correctly

Disregard, I read the question incorrectly. I'll follow up if I have a better solution.

 

I use the following calculations to separate out the date periods and calculate YoY% Change

 

Period Category

(CASE  WHEN ((`Date` >= subdate(CURRENT_DATE()-(DAYOFYEAR(CURDATE())-1+92),interval 12 month)) AND (`Date` < (CURRENT_DATE())-(DAYOFYEAR(CURDATE())-1+92))) THEN 'Prior Year' WHEN ((`Date` >= (CURRENT_DATE())-(DAYOFYEAR(CURDATE())-1+92)) AND (`Date` < CURRENT_DATE())) THEN 'This Year' END )

 

YoY% Calculation (this takes you back to October 1st, if you just want to get to January remove the +92).

((Sum((CASE  WHEN ((`Date` >= (CURRENT_DATE())-(DAYOFYEAR(CURDATE())-1+92)) AND (`Date` < CURRENT_DATE())) THEN `Visits` END )) / Sum((CASE  WHEN ((`Date` >= subdate(CURRENT_DATE()-(DAYOFYEAR(CURDATE())-1+92),interval 12 month)) AND (`Date` <= Subdate(CURRENT_DATE(),interval 12 month))) THEN `Visits` END ))))-1

Dojo Admin

Re: YOY % Calculation not working correctly

Hollowman,

 

You can also look at this post which won first place in our January 2016 contest.

 

Regards,

Dani

Dani aka "Mr.Dojo"

Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
White Belt

Re: YOY % Calculation not working correctly

Thanks DaniBoy, however the problem in this case was that the user wanted to adjust the time frame and have the period over period calculation be dynamic. I believe I have come up with a solution to this and have posted it here. Let me know what you think.

 

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!