I have an item list per department along the different ordered quantities and price per item over different periods. I need to compute the inflation rate between two periods. The formula : Qty_N(Price_N - Price_N-1) / (Qty_N*Price_N-1) i can show it with two pivot table :
1)
Department
Item
Qty ordered 2016
Qty ordered 2017
Price 2017
Inflation rate
24
A
0
6
57
-
24
B
0
123
230
-
24
C
16
0
8
-
24
D
510
891
35
0.747
Dimensions: Department , Item [Light Blue] Expressions : -Qty : sum(<year=2016>qty) -Price : avg(<year=2017> price) -Inflation: as formula
2)
Department
Item
Year
Qty ordered
Price
Inflation rate
24
A
2017
6
57
-
24
C
2016
16
8
-
24
D
2016
510
30
-
24
D
2017
891
35
0.747
Dimensions: Department , Item, Year
Expressions :
-Qty : sum(qty)
-Price : avg(price)
-Inflation: as formula but using above function : { col(1) * [col(2)-above(col(2))] } / { col(1) * above(col(2))}
However, I want to compute the average rate of inflation per department If I just remove the dimension Item, Qlik will simply add all quantities per year, regardless of their presence in succesive years or not ==> Wrong result.
Thus I would like to know how can I subset one of the pivot table to show only the right row.
Data Source: Qvd file of all retail transactions regarding orders: which item ordered to supplier plus quantity,price,date etc...
