Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
george55
Partner
Partner

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.

2020.0302-193759.jpg

 

TeamCalls TeamCalls Department
Total974925
Team 111
Team 2467467
Team 3506506

 

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)
1 Solution

Accepted Solutions
george55
Partner
Partner
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))

View solution in original post

5 Replies
sunny_talwar

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?

george55
Partner
Partner
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.

dwforest
Specialist II
Specialist II

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

george55
Partner
Partner
Author

no improvement, same results

george55
Partner
Partner
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))

View solution in original post