Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
silviavan
Contributor
Contributor

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.

RegionProductSub_group productInventoryDemandCoverage
A1a1052.0
b2054.0
2a1081.3
b2082.5
c3083.8
B1a10101.0
b20102.0
2a1061.7
b2063.3
c3065.0
3c30301.0

Any suggestion or help is very much appreciated!

Best Regards,

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

5 Replies
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)

silviavan
Contributor
Contributor
Author

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

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)

silviavan
Contributor
Contributor
Author

Works like a charm! Thank you!

maximiliano_vel
Partner
Partner

Please see Attached File