topic Re: Min and Max in pivot chart based on category in QlikView

Min and Max in pivot chart based on category

Anonymous — Tue, 27 Mar 2018 03:09:28 GMT

Hi all,

I am new to QV, and have been struggling to find appropriate expression for my chart in all discussion topics. I am building a chart with three dimensions: Product region, product, sub_group product, and I calculated the inventory coverage based on Inventory/Demand.

I am supposed to find out the sub-group product within each product group that has lowest inventory coverage (highlight in red) , and filter out everything else, but couldn't figure out a way to do that.

Region	Product	Sub_group product	Inventory	Demand	Coverage
A	1	a	10	5	2.0
	1	b	20	5	4.0
	2	a	10	8	1.3
		b	20	8	2.5
		c	30	8	3.8
B	1	a	10	10	1.0
	1	b	20	10	2.0
	2	a	10	6	1.7
		b	20	6	3.3
		c	30	6	5.0
	3	c	30	30	1.0

Any suggestion or help is very much appreciated!

Best Regards,

Re: Min and Max in pivot chart based on category

sunny_talwar — Tue, 27 Mar 2018 14:01:50 GMT

May be this

If(Inventory/Demand = Min(TOTAL Aggr(Inventory/Demand, [Product region], product, sub_group product)), Inventory)

If(Inventory/Demand = Min(TOTAL Aggr(Inventory/Demand, [Product region], product, sub_group product)), Demand)

If(Inventory/Demand = Min(TOTAL Aggr(Inventory/Demand, [Product region], product, sub_group product)), Inventory/Demand)

Re: Min and Max in pivot chart based on category

Anonymous — Tue, 27 Mar 2018 16:11:27 GMT

Thank you for replying! However it only gives me one line of sub_group product with lowest coverage across all region and products...

Re: Min and Max in pivot chart based on category

sunny_talwar — Tue, 27 Mar 2018 16:17:46 GMT

May bad... try this

If(Inventory/Demand = Min(TOTAL <Region, Product> Aggr(Inventory/Demand, [Product region], product, sub_group product)), Inventory)

If(Inventory/Demand = Min(TOTAL <Region, Product> Aggr(Inventory/Demand, [Product region], product, sub_group product)), Demand)

If(Inventory/Demand = Min(TOTAL <Region, Product> Aggr(Inventory/Demand, [Product region], product, sub_group product)), Inventory/Demand)

Re: Min and Max in pivot chart based on category

Anonymous — Tue, 27 Mar 2018 16:26:15 GMT

Works like a charm! Thank you!

Re: Min and Max in pivot chart based on category

maximiliano_vel — Tue, 27 Mar 2018 16:40:35 GMT

Please see Attached File