Comparing a Measure and a dimension and selecting the max value

[Pratik81]

Hi All, 

I am relatively new to qlik and i have a situation where i want to compare  Sum of Qty with Target set and select the max incentive for the the corresponding value. Incentive achieved is the Max incentive * Sum of Qty. 

Would greatly appreciate some help here. 

Thank you

Customer	Sum of Qty	Tier Target Qty	Incentive	Incentive Achieved
Customer A	90	50	10
Customer A	90	60	20
Customer A	90	80	30	2700
Customer B	30	20	10
Customer B	30	30	20	600
Customer B	30	40	30

[mpc]

Hi,

Maybe you can use Max(total Incentive) ? It will ignore Customer dim and take the Max for all the table

Best

[Pratik81]

Thanks for your response. I think if i do the Max(total Incentive) then it will always provide one value that is 30 which is max incentive in the table.

However this might not be case as can be seen for customer B where the Sum of Qty achieved (30) is lower than the Tier Target Qty (40) which does not qualifies the customer B for Incentive (30)

Hence the customer does not qualify for the incentive (30) but qualifies for the tier lower which is (20). Hence what i am trying to achieve in the table in a measure (Incentive Achieved) that calculates the total incentive recieved by Customer B as 30* 20= 600

[mpc]

Quite more complex indeed. 
At glance I think you can achieve this Aggr function. I'll test it by myself and send you a QVF. 

Best

[Pratik81]

Would really appreacite that. Thanks for looking into it.