4 Replies Latest reply on Aug 7, 2018 4:47 AM by Simon Runc

# How to calculate Year on Year change with previous year % change

Hi,

I am trying to calculate a 1% increase in our staff numbers to existing staff numbers for period 2012 to 2017.

This is what I am trying to achieve :

I am using below formula. But the below formula is not doing what we want. Can any one help out ?

if INDEX()=1

then

sum([SIP])

ELSE

sum([SIP])+lookup(running_sum(sum([SIP])),-1)*0.01

END

Thanks,

Ben

Attached packaged workbook.

Mavis Liu

Zhouyi Zhang

Simon Runc

• ###### 1. Re: Percentage Change Year on year

Hi, Benedict

Hope this helps

ZZ

• ###### 2. Re: Percentage Change Year on year

Hi, Benedict

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Percentage Change Year on year

Sorry not there quite yet. The calculation I am using in excel is as below and the result is below as well

1 of 1 people found this helpful
• ###### 4. Re: Percentage Change Year on year

hi Ben,

Here you go.

So first I created a formula to pick up the first year (so we can equate this to NULL). I used an LoD, but we could have used a Table Calc

I then created the 1%

[SIP - 1%]

[SIP]*0.01

and then the final version combined this like so

[Expected Value]

IF MIN([Year]) = MIN([Min Year]) THEN NULL

ELSE LOOKUP(SUM(SIP),-1) + LOOKUP(SUM([SIP - 1%]),-1)

END

This is run across year.

I've done it in Table Form, and in the Viz so you can see what's going on

Hope that helps