Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Lars_Esser
Contributor II
Contributor II

Wrong result Count distinct after split of data

We have the following bar chart: 

Knipsel1.PNG

Dimensions are YEAR en TYPE CREDITOR, Meassure is a Distinct count of the CREDITOR NUMBERS. 

In Qlik we want to spit a certain type ("Crediteur" - 1564 items). 

We do this by making a new masteritem (dimension) with the following expression: 

=if ([CRED_DIM_TYPE_CREDITEUR] = 'Crediteur',
if(Match(ID_CATEGORIE_LEVEL_3, 'HR.07.02','HR.07.03','HR.13.03','HR.13.02','HR.09.06',
'FAC.06.01','FAC.06.02','FAC.03.03','AM.07.10','HR.08.03',
'FAC.06.03','HR.06.01'),'Geaccepteerde crediteur','Niet Geaccepteerde Crediteur'),
CRED_DIM_TYPE_CREDITEUR)

When we change the the dimension of the chart to this new one we get: 

Knipsel2.PNG

The result of the Count Distinct is different, while the source data is not changed. 

We see 1564 items for "Niet Geaccepteerde crediteur" in stead of 1212

352 items for "Geaccepteerde crediteur" are correct. 

1564-352 =1212

So it seems like the split does not work in our bar chart

 

 

Labels (3)
1 Solution

Accepted Solutions
Lars_Esser
Contributor II
Contributor II
Author

Solved; Used a wrong field in the creation of the dimension.

View solution in original post

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

I would expect count distinct to produce different results if you change the dimension. The coarser bucket may land up ignoring a duplicate which is not duplicated in the finer resolution buckets. So the finer the granularity, the higher the total of the distinct count will be.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Lars_Esser
Contributor II
Contributor II
Author

I don't understand what u mean; 

for example:

100 unique creditors have type A

I split type A in 50 type A1 and 50 Type A2

I still have to have 100 unique creditors. 

Lars_Esser
Contributor II
Contributor II
Author

Solved; Used a wrong field in the creation of the dimension.