How do I write the syntax for YOY % change?

damen
damen Contributor
edited November 2022 in Beast Mode

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.

If this helps, feel free to agree, accept or awesome it!

Answers

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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • damen
    damen Contributor

    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?

    If this helps, feel free to agree, accept or awesome it!

  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • damen
    damen Contributor

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

    If this helps, feel free to agree, accept or awesome it!

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • damen
    damen Contributor

    Well, dang. Thanks for the link

    If this helps, feel free to agree, accept or awesome it!