Min and Max in pivot chart based on category

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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

[sunny_talwar · Accepted solution]

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)

[Anonymous]

Works like a charm! Thank you!

[maximiliano_vel]

Please see Attached File