Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Min and Max in pivot chart based on category

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,

1 Solution

Accepted Solutions
MVP

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)

5 Replies
MVP

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
Not applicable
Author

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

MVP

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
Not applicable
Author

Works like a charm! Thank you!

Partner - Creator III