Issue with 'distinct' Clause in Pie Chart due to Data Duplication

[LHerbay]

Hi everyone,

I’m facing an issue in Qlik Sense where the distinct clause in my measure is not being correctly reflected in a pie chart visualization.

The Scenario:

I’m using a measure like this:

count({< FactType={'career'} >} distinct CareerEmployeeId)

In my dataset, it’s possible for the same CareerEmployeeId to have multiple CareerContractType values for the same period. This seems to cause the pie chart to count these duplicates when segmenting by the dimension CareerContractType, even though I’m using distinct in the measure.

(the pie chart with a count of CareerEmployeeID by CareerContractType)

(the KPI with the exact same measure, showing the correct count result I'd like to obtain in the pie chart)

My Hypothesis:

I believe the solution might involve using the Aggr() function or a similar approach to pre-aggregate the data, ensuring only the most recent CareerContractType is considered for each CareerEmployeeId. However, I’m unsure how to structure this properly or in what order to approach it.

My Question:

How can I handle this issue to ensure the distinct clause is respected in the pie chart? Should I use Aggr() to filter out duplicates (e.g., keeping only the last contract type), and if so, how would I integrate that into my measure?

I’d greatly appreciate any guidance or best practices to resolve this.

Thank you!

(here the corresponding data with the duplicated CareeremployeeId)

[Or · Accepted solution]

This is correct behavior based on the data - the distinct count is applied per dimension (as is the entire expression, of course), and since one employeeID appears in two dimensions, it will be counted twice.In this scenario, there is no way to present the information that would result in 10, because there's no way for Qlik to decide which of the contract types to keep for a given employee (they even share the same timestamp, so you couldn't use any sort of FirstSortedValue() based solution). 

You could create a subset of the information by getting the first row (sorted in some arbitrary way past the date) in your script, and then use that.