I'm trying to write an expression which requires capping the count of certain dimension based on another dimension. the capping is done properly by If equation however the totals aren't calculated properly.
In this example:
Capped Calls = If (Actual Calls > Target Calls, Target Calls , Actual Calls)
The expression works fine on the level of the Customer (Cust_ID). but in totals the capped calls become inaccurate.
the total of Capped Calls should be 10+30+30+10= 80 Calls. but in Qlik it compares count of all actual calls which is 10+40+30+10= 90 with total count of target calls which is 20+30+40+10= 100 & since 90 is less than 100 the total is 90 instead of 80.