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

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?

• 1. Re: Create YoY Calculation with Hierarchy and Taking Missing Years Into Consideration

HI Sarah

Hope this helps.

LOD

Overview: Level of Detail Expressions

Thanks,

Shin

• 2. Re: Create YoY Calculation with Hierarchy and Taking Missing Years Into Consideration

Thank you so much Shin! This worked beautifully.

• 3. Re: Create YoY Calculation with Hierarchy and Taking Missing Years Into Consideration

Hi Sarah,

You are very welcome.

Shin

• 4. Re: Create YoY Calculation with Hierarchy and Taking Missing Years Into Consideration

Just a side note, the following helped to remove the rows that did not contain the correct year from the overall calculation so that I was able to get like-for-like YoY change: