Count Expression to get reflected in Total

[ahmed_eihab]

Hi All. 

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. 

Any idea how to make total equals 80 not 90? 

SR Login	FSM Login	NSM Login	Target Calls	Actual Calls	Capped Calls	Cust_ID
SR1	FSM1	NSM1	20	10	10 (Less than Target then consider Actual)	0012u000003T6AnAAK
SR1	FSM1	NSM1	30	40	30 (More than Target then consider Target)	0012u000003T5waAAC
SR1	FSM1	NSM1	40	30	30 (Less than Target then consider Actual)	0012u000003T69sAAC
SR1	FSM1	NSM1	10	10	10 (Same)	0012u000003T5vbAAC

[chrismarlow]

Hi,

Have you tried for your Capped Calls measure change the Totals Function to Sum? 

Cheers,

Chris.

[ahmed_eihab]

Thanks a lot Chris. 

This did the needful in the Tables. Thanks a lot 😊

but is there a way to do the same in other Charts? 
for example if I created a Bar Char to calculate number of capped calls for SR login: SR1, the capped calls will be 90 instead of 80. 

Shall I add something in the expression to do the capping on the level of each of customer?

thanks a lot in advance. 

[chrismarlow · Accepted solution]

Hi,

I think you would use AGGR, so something like;

sum(aggr(If (sum([Actual Calls]) > sum([Target Calls]), sum([Target Calls]) , sum([Actual Calls])),[Cust ID]))

You may need more than just Cust_ID to 'AGGR' over (your FSM/NSM fields maybe ...), but start with above.

Cheers,

Chris.

[ahmed_eihab]

Thanks a lot Chris for your help 🙂 this expression worked perfectly for me. 

Thanks again :).