3 Replies Latest reply on Mar 7, 2017 1:15 PM by Ivan Young

    Using a calculated field as a reference for YoY calculations?

    Lily Huang

      I have a constrictive data source that does not allow me to solve this problem at a data cleaning level. Attached is a sample worksheet with the problem I am trying to solve.


      Basically, my data source has a date, value and a property (x or y). I need to construct a table that for every year shows the following:

      - Value of X

      - Value of Y

      - Y as a % of X

      - YoY change of Y as a % of X


      What I've done so far is used "0" columns to separate out the variables of interest so I can apply color formatting. I've used a calculated field to calculate Y as a % of X using a LOOKUP along property. However, when I try to calculate YoY change using LOOKUP along date, I am left with the wrong value or an empty cell. Any tips?


      Thanks for your time.