4 Replies Latest reply on Mar 19, 2018 7:32 AM by laura shal

# Creating a year over year variance with multiple years of data available

Good afternoon,

I am currently working on a report
using crosstabs with data available for the years 2016, 2017, and 2018. A
column I have in my crosstab are the variances to compare one year to another
(for example: 2018 v 2017). Last year, I had no problem showing the variances
here because I could just use a quick table calculation for 2016 and 2017 and
then hide the 2016 column. However, with 2018 data now available, I would like
the end users to be able to filter between 2016, 2017, and 2018 and be able to
compare any two years at a time. What I noticed was happening on my end is that
since I have 3 years of data I can choose from, the hiding a column method does
not work anymore.

Would there be any workaround this?

• ###### 1. Re: Creating a year over year variance with multiple years of data available

Hi Laura,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

1. NM D1. Year: year([date])

2. NM M1. 2016: if [NM D1. Year]=2016 then [value] END

3. NM M2. 2017: if [NM D1. Year]=2017 then [value] END

4. NM M3. 2018: if [NM D1. Year]=2018 then [value] END

5. NM P1  Year High and NM P2 Year Low

6. NM M4. Year Low:

if [NM P2. Year low]='2016' then [NM M1. 2016]

elseif [NM P2. Year low]='2017' then [NM M2. 2017]

elseif [NM P2. Year low]='2018' then [NM M3. 2018]

END

7. NM M5. Year High

if [Nm P1. Year high]='2016' then [NM M1. 2016]

elseif [Nm P1. Year high]='2017' then [NM M2. 2017]

elseif [Nm P1. Year high]='2018' then [NM M3. 2018]

END

8. NM M6. Delta:sum([NM M4. Year Low])-sum([NM M5. Year High])

9. Drag the required objects to the indicated locations and show parameter control

Regards,

Norbert

• ###### 2. Re: Creating a year over year variance with multiple years of data available

Hi Norbert,

Thank you for getting back to me so quickly! Is it possible that you could share the 10.2 version of the workbook? I want to make sure that your instructions are compatible with my system.

Thanks again ☺

• ###### 3. Re: Creating a year over year variance with multiple years of data available

Hi Laura,

Find attached in version 10.2.

Regards,

Norbert

• ###### 4. Re: Creating a year over year variance with multiple years of data available

Thanks for the response! Would it be possible to do this for multiple measure values as well? For example, if I have many different measure values going down the row instead of months with dates in the column, what would be the best way to calculate the variances?