Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Hi,
Have you tried for your Capped Calls measure change the Totals Function to Sum?
Cheers,
Chris.
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.
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.
Thanks a lot Chris for your help 🙂 this expression worked perfectly for me.
Thanks again :).