Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
ahmed_eihab
Contributor II
Contributor II

Count Expression to get reflected in Total

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 LoginFSM LoginNSM LoginTarget CallsActual CallsCapped CallsCust_ID
SR1FSM1NSM1201010 (Less than Target then consider Actual)0012u000003T6AnAAK
SR1FSM1NSM1304030 (More than Target then consider Target)0012u000003T5waAAC
SR1FSM1NSM1403030 (Less than Target then consider Actual)0012u000003T69sAAC
SR1FSM1NSM1101010 (Same)0012u000003T5vbAAC
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

 

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

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

20210621_1.png

Cheers,

Chris.

ahmed_eihab
Contributor II
Contributor II
Author

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
Specialist II
Specialist II

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.

 

View solution in original post

ahmed_eihab
Contributor II
Contributor II
Author

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

 

Thanks again :).