Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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)
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?