YoY YTD Comparison

user03929

I am trying to build a chart that compares YoY growth across cities.  X axis would be the city, primary Y axis would be revenue, secondary Y axis would be % change, and the series(grouped bar) would be YTD revenue, and Prior Year YTD revenue.  

 

I can't seem to do this in the YoY charts because I need to show the cities on the X axis.  It seems I need the proper BeastMode calculations to set this up.  Can someone point me to a way to calculate current year YTD revenue, PY YTD revenue, and the % change between those two?  Full year does not work because is not an apples to apples comparison as 2018 has just started.  

 

The attached screenshot is a general idea of how I'm trying to organize the data.

 

Thank you!

 

 

BlueRooster

Here's a link where I explained how to do this for someone else (except instead of Cities, we used MonthNames as the X axis). You should be able to skip that part but the year over year beastmodes should still apply.

https://dojo.domo.com/t5/Card-Building/Re-Period-over-Period-using-stacked-bars/m-p/30479#M3655

 

Let me know if you have any questions.

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

BlueRooster

Forgot to add, the formula for % change is just a combination of the two year beastmodes.

Something like this:

(Current Year BeastMode / Previous Year BeastMode) - 1

 

And if you're only showing YTD, then just set the Date Range to Year to Date.

 

Hope this helps

 

user03929

Thanks for the quick response!  As of now I am getting a syntax error on the CY BeastMode with '%Y-%m-01' highlighted in red.

BlueRooster

So you're wanting to compare this year to date vs last year to date, is that correct?

 

Also, was is the name of your date field? And are you going to be counting or summing your value field? 

 

If I know the names of those fields, I'll just write it up for you real quick

user03929

Exactly.  

As of 2/8/18 there is $X in revenue as one bar in the stack.  As of 2/8/17 there was $X revenue as one bar in the stack.  Grouped by city and summing the number.

 

The date field is 'close date' and the $ field is just 'revenue'.

 

I stumbled upon this as a PY solution:

 

SUM(CASE WHEN `Close Date` <= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) THEN `Revenue` END)

BlueRooster

Here you go:

 

Prior Year: 

SUM(CASE WHEN `Close Date` <= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)  AND `Close Date` >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR), '%Y-01-01')
THEN `Revenue` END)

 

Current Year:

SUM(CASE WHEN `Close Date` >= DATE_FORMAT(CURDATE(), '%Y-01-01')
THEN `Revenue` END)

 

Variance:

(SUM(CASE WHEN `Close Date` >= DATE_FORMAT(CURDATE(), '%Y-01-01')
THEN `Revenue` END)
 /
 SUM(CASE WHEN `Close Date` <= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)  AND `Close Date` >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR), '%Y-01-01')
THEN `Revenue` END)
 )
 - 1

 

And then just use your city field as the X axis. Let me know if you have any issues.

bof

We look at a ton of YOY comparisons. The best way we have found is in the dataflow(SQL or Redshift) we have columns for "current year sales" and "py sales" then we create a beastmode like sum(cy sales)-sum(py sales) or for the % (sum(cy sales)-sum(py sales))/sum(py sales). 

 

That approach allows you to throw anything on the axis and the beastmode will calculate