topic Re: Inflation Index calculation in Chart in QlikView

Inflation Index calculation in Chart

Thu, 25 Sep 2014 09:06:48 GMT

Hello,

I have an issue with a calculation. I'm using the Laspeyres index formula to define an inflation.

It is working this way :

where Pt;i = Price at the moment (ex : August 2014)

Q0;i = Reference Quantity (Ex : taking January 2014 as reference, it's the quantity sold in January)

P0;i = Reference Price (Price of the product in January 2014)

This leads to an index which will be 100 in January and will evolve month by month, regarding the price inflation.

I want to show it in a chart (pivot table) like this :

however, i'm currently unable to make my index work for the other months.

I can't pick the reference Price in the calculation, it fails.

As I am a bit out of ideas, do someone have any lead ?

I attached a sample of data with the model.

Thanks by advance.

Best Regards, !

Re: Inflation Index calculation in Chart

Thu, 25 Sep 2014 09:20:52 GMT

You need to change the price in the denominator to show january, regardless of the month in the dimension.

/to disregard the dimension,you can use the <total> function. Below, I've totalled it as the total for the product description. Then use set analysis to pick out january.

Something like this:

avg({<MOIS_FAC={'2014-01'}>} total <[Product Description (EN)]> PA)

Erica

Re: Inflation Index calculation in Chart

Thu, 25 Sep 2014 09:24:23 GMT

The function I end up with is

avg(PA)/

avg({<MOIS_FAC={'2014-01'}>} total <[Product Description (EN)]> PA)*100

The quantities reduce each other out as you are doing it by product, so you don't need them unless you are grouping them up.

Erica

Re: Inflation Index calculation in Chart

Thu, 25 Sep 2014 12:32:44 GMT

Hello Erica,

Thanks for having a look.

However, i'm using the sum function at the end to be able to group the values at a higher level.

Here is what it looks at a higher level. I should get the index for each row, however i only get a real index at the total row.

The quantity is used to weight the product price at a higher level.

Re: Inflation Index calculation in Chart

Fri, 26 Sep 2014 07:28:56 GMT

Does anybody else encountered this ?

Re: Inflation Index calculation in Chart

Fri, 26 Sep 2014 08:06:02 GMT

I hadn't noticed the aggregation

You can add the invoiced quantity in the expression, inside a sum. I think this works:

sum(PA * [Invoiced Quantity in January])/

sum({<MOIS_FAC={'2014-01'}>} total <[Product Description (EN)]> PA * [Invoiced Quantity in January])*100

Infact it almost mimics the formula!!

What totals are you expecting?

Erica

Re: Inflation Index calculation in Chart

Mon, 29 Sep 2014 07:38:39 GMT

You're right, I've made some tests and it looks good, totals too.

Thanks a lot for the help.

Amaury