Count Distinct doesn't want to count

[MikeJones]

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

[Chanty4u]

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]

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?