How to subset a Pivot Table ?

Anonymous — Thu, 16 Nov 2017 10:44:20 GMT

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 :

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

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.

Re: How to subset a Pivot Table ?

sunny_talwar — Thu, 16 Nov 2017 16:45:13 GMT

You can remove dimension and use an Aggr() function to get what you might be looking to get?

topic How to subset a Pivot Table ? in QlikView

How to subset a Pivot Table ?

Re: How to subset a Pivot Table ?