topic Re: Need help with cumulative inflation table... in QlikView

Need help with cumulative inflation table...

Tue, 16 Oct 2012 16:01:57 GMT

I have two base column expressions that I need to project out with a defualt 1.02 inflation rate and have it cumulatively go across each year.

%Inflation=1.02

This one remains untouched

Sum({<[Cost_Type]={'Revenue'}>} Cost_Value)

This one will be cumulative

Sum({<[Cost_Type]={'Cost'}>} Cost_Value * %Inflation)

Im trying to replicate the excel table as attached.

Re: Need help with cumulative inflation table...

Tue, 16 Oct 2012 16:54:51 GMT

You need to multiply with %Inflation ^ (Year - Base Year) e.g. 1.02 ^ (2014 - 2012).

Re: Need help with cumulative inflation table...

Tue, 16 Oct 2012 17:18:10 GMT

I translated your statement to: %Inflation * ([P/L 2013] - [P/L 2012])

It returned 0, I'm sure I missed a crucial piece.

The data is only derived from 2011 so I dont have 2012 as actual data, simply projecting it based off the inflation value.

I never used the ^ what is the equivalent to the power symbol?

Re: Need help with cumulative inflation table...

Gysbert_Wassenaar — Tue, 16 Oct 2012 17:21:30 GMT

Assuming you have a Year dimension, maybe something like this:

Sum({<[Cost_Type]={'Cost'} > } Cost_Valuie * pow(%inflation, max(Year) - Year ) ) )

edit: changed it to something that makes some more sense. Also maybe using a variable %resultyear instead of max(year) to be able to choose a year instead of being limited to the max year value in your data.

Re: Need help with cumulative inflation table...

Tue, 16 Oct 2012 18:45:26 GMT

I'm going to try this. My friend suggested I use the pow function except I have to keep appending the inflation variable for each additional year. Is there a better way?

Base Rev Column - Base Total Costs * Pow(%Inflation,1+N)