# New to Qlik Sense

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

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
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 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
Labels (6)

• ### TOTAL of column

1 Solution

Accepted Solutions
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.

4 Replies
Specialist II

Hi,

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

Cheers,

Chris.

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?

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.

Contributor II
Author

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

Thanks again :).

Tags
Community Browser