4 Replies Latest reply on Jul 12, 2018 7:04 AM by Sarah Sidoli

    Create YoY Calculation with Hierarchy and Taking Missing Years Into Consideration

    Sarah Sidoli

      Hi there,


      I've been working on this problem for a couple of days and I'm finding that I'm at the end of my wick.


      I believe my problem is two fold. I am looking to build a calculation that will:

      1. Create a Percent Difference Year on Year

      2. Remove a value from one year, if the other year is not present


      I have created an example table of the kind of data I am looking at. My data looks at regions, and within those regions certain subjects are chosen by students who want to study that subject. I have been asked to report on year on year increase in the number of students that have applied to study certain subjects at a regional level. This has been easy enough task using a simple table calculation. My problem is being able to report like for like.


      If I were to break down the numbers by subject, not all subjects were chosen in all the years. So in Dorset, the number of students who selected History are inaccurately boosting the YoY % difference. This is only a snippet of the kind of data I am looking at, as there are other sections of data where there are missing values in 2017.

      What I would like to be able to do is create a calculation that will create a YoY % difference, on a regional level, but does not include a subject that does not have both years of data.


      Is there a straightforward way I can do this?


      Thank you for your help in advance!