2 Replies Latest reply on Oct 3, 2016 2:40 AM by Huseyin Seker

# Calculate index between periods. The period must be changed by parameters

Hi,

I want calculate the index, percent difference,  between periods  (Years). The years must be changed with a parameter.

For example

If I choose 2007 the calculation must be based on 2007=100. The years must be choosed by a parameter.

(ZN(SUM([Aantal_wp])) - LOOKUP(ZN(SUM([Aantal_wp])), FIRST())) / ABS(LOOKUP(ZN(SUM([Aantal_wp])), FIRST())).

This calculation works only if i want the difference when 2007=100.

How can i change the command FIRST with a parameter. So i can change the year which = 100  example 2009

 PERIOD Employers 2007 2008 2009 2010 2011 2012 2013 2014 2015 2007 2148 100 2008 2360 110 100 2009 2295 107 97 100 2010 2046 95 87 89 100 2011 2170 101 92 95 106 100 2012 2123 99 90 93 104 98 100 2013 1894 88 80 83 93 87 89 100 2014 1803 84 76 79 88 83 85 95 100 2015 1801 84 76 78 88 83 85 95 100 100
• ###### 1. Re: Calculate index between periods. The period must be changed by parameters

Is PERIOD formatted as a number, a string or a date? Also, is your PERIOD parameter formatted as a number, string or date?

If they are numbers, try replacing both instances of FIRST() with -(ATTR([PERIOD])-[PERIOD Parameter])

If they are strings, convert them to numbers and use the above formula.

If they are dates, use -(ATTR(YEAR([PERIOD]))-YEAR([PERIOD Parameter]))

2 of 2 people found this helpful
• ###### 2. Re: Calculate index between periods. The period must be changed by parameters

Thank you Benjamin

it works partially. If  I choose 2007 (period parameter) then shows the table 2007 to 2011. (2007 = 0,00= correct).

2012 to 2015 is not shown. This is not correct.

If i choose 2010 then shows the table 2009, 2010, 2011 and 2012. (2010 = 0,00 = correct)

But why are 2013, 2014 and 2015 empty?

2009 must not be showed