Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inflation Index calculation in Chart

Hello,

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

It is working this way :

6a66fc7c1b7063477451232aa10b6b3b.png

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 :

2014-09-25 11_00_16-QlikView x64 - [Q__QV_Publisher_Dev_PUBLISH_DATAWARE_CPI_CPI_community.qvw_].png

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, !

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

2014-09-25 14_30_20-QlikView x64 - [Q__QV_Publisher_Dev_PUBLISH_DATAWARE_CPI_CPI_community.qvw_].png

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

Not applicable
Author

Does anybody else encountered this ?

Not applicable
Author

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

View solution in original post

Not applicable
Author

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

Thanks a lot for the help.

Amaury