Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
silviavan
New 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,

Tags (2)
1 Solution

Accepted Solutions

Re: Min and Max in pivot chart based on category

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

Re: Min and Max in pivot chart based on category

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
New Contributor

Re: Min and Max in pivot chart based on category

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

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

silviavan
New Contributor

Re: Min and Max in pivot chart based on category

Works like a charm! Thank you!

Highlighted
Partner
Partner

Re: Min and Max in pivot chart based on category

Please see Attached File