Hi all,
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...
Thanks a lot for your attention.
