How do I write the syntax for YOY % change?

damen

Hi all,

I am trying to create a calculate field where I am able to see only the change from year to year in average purchase price.

I understand the math to be purchase price1 - purchase price 2 / purchase price 1 but I am not getting the correct output when I try the syntax

Here is what i have so far:

Any suggestions on how to write the correct syntax for this formula?

I have tried writing it a few different ways and keep getting syntax errors.

GrantSmith

You'll want to do something like:

(
  AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE()) THEN `purchase_price` END)
-
  AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END)
)
/
AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END)

damen

So I think by using the CURDATE function in there its only wanting to give me the change in this specific year.

You can see at the bottom there is average income numbers filled in the 2018 year. How would I change that syntax to include those years in the visualization as well?

GrantSmith

What you'll want to do then since you want historical values you can configure a date dimension dataset to then compare the data to itself with a specific offset (in your case the year). I've done write ups on how to do this here: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

damen

@GrantSmith Hopefully this comes off the right way but is that the simplest way to do it?

GrantSmith

It may seem a bit complex but because you're wanting to get YoY for dates other than this year this is the best option.

damen

Well, dang. Thanks for the link