Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Partner - Creator III

## Aggregation with distinct values

Situation: 1 Department with 2 Teams. The Teams are obtaining calls. Some calls can be transfered to the other team. Calls have to be count. Two methods for counting:

1) Team level
Speciality: When a call have been transfered to the other team, then it should count for both teams.

2) Department level
Transfered calls should not be counted twice as on Team level.

 Team Calls Team Calls Department Total 974 925 Team 1 1 1 Team 2 467 467 Team 3 506 506

Using expressions:

Calls Team
Sum(Aggr(Count(Distinct {<[Calldirection]={'Inbound'}>} [CallId]), [%Team]))

Calls Department
Count(Distinct {<[Calldirection]={'Inbound'}>} [%CallId])

All numbers in the TableBox are correct except of the red one's.
Any idea how to count correct?

Labels (1)
• ### SaaS

1 Solution

Accepted Solutions
Partner - Creator III
Author

Find the solution. my conclusion:

1) Needed in both cases Sum(Aggr(..., %Team))
2) Enhanced the business logic. Subtracted the quantity when a transfer has occured. This is also now part of the Sum(Aggr(..., %Team))

5 Replies
MVP

Where would you want to show the calls? Team 2 or Team 3? Both of them were part of the calls at one time, right? The first team that got the call?

Partner - Creator III
Author

Both teams get one count when call is transfering from one to the other team. This is reflecting in the data with the same %CallID. Column Team level has a little bit an other treatment to count (one dimension is different).

In other words. When use Count(Distinct {<[Calldirection]={'Inbound'}>} [%CallId]) in a standalone object then I get this numbers:

Team 1: 459
Team 2: 466

The total of these numbers are associating with the Total showing in the table on department level. Would like to show these numbers embedded in the table/pivotbox.

Specialist II

Count(Distinct {<[Calldirection]={'Inbound'},[%Team]=>} [%CallId])

Partner - Creator III
Author

no improvement, same results

Partner - Creator III
Author

Find the solution. my conclusion:

1) Needed in both cases Sum(Aggr(..., %Team))
2) Enhanced the business logic. Subtracted the quantity when a transfer has occured. This is also now part of the Sum(Aggr(..., %Team))

Tags
Community Browser