Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeJones
Creator
Creator

Count Distinct doesn't want to count

Hi, I am trying to count the number of distinct agents working.  Due to data confidentiality, I can’t send the data.

I wish to count the number of distinct agents working for each week from all of my main 3 data tables.  There can be instances where data is showing in one table only for agent X on a specific date.

I am using Count(DISTINCT {<[Date-LiveDate.autoCalendar.Date] = {"*"}>} Alt(FlagOwner, user_name, Fullname))

The table below shows the number of distinct agents from the 3 different sources for a certain period.  The final column shows the Qlik output.

MikeJones_0-1743604793261.png

I have 4 data tables.

Date – this lists each date from 1st October 2024 to yesterday.  This is my master date table.

Calls  - field user-name shows the Agent.  Calls.Date links to Date.LiveDate

Meters – field Fullname shows the Agent.  Meters.BookingDate links to Date.LiveDate

Time – field Flagowner shows the Agent.  Time.Date links to Date.LiveDate

Labels (5)
2 Replies
Chanty4u
MVP
MVP

Try this 

Agents:

LOAD 

    [Date-LiveDate.autoCalendar.Date] AS Date,

    user_name AS Agent

RESIDENT Calls

WHERE Len(Trim(user_name)) > 0; // Ensure no nulls

 

CONCATENATE(Agents)

LOAD 

    [Date-LiveDate.autoCalendar.Date] AS Date,

    Fullname AS Agent

RESIDENT Meters

WHERE Len(Trim(Fullname)) > 0;

 

CONCATENATE(Agents)

LOAD 

    [Date-LiveDate.autoCalendar.Date] AS Date,

    FlagOwner AS Agent

RESIDENT Time

WHERE Len(Trim(FlagOwner)) > 0;

 

 

In UI 

Count(DISTINCT Agent)

 

 

MikeJones
Creator
Creator
Author

Hi Chanty4u, thanks for your time posting a reply. 

I don't have the necessary skills to use the load editor and I need all the data to come in.  Therefore, I don't wish to introduce any where clauses.

Is it possible to what i wish in set analysis?