Count distinct not working as expected in set expression

[patricesalem]

Hello 

Thanks to @tresesco , I was able to fix a first issue using count distinct in a set expression in order to calculate the number of orders late in my qlik detailed straight table :

I would like now to calculate the number of orders shipped during each "Planned Shipment week".

So in a second straight table (the first one is fully detailed including line orders), I summarize the data of the first table :

The excel table tells me that I have 406 orders shipped on week 39.

The following expression in my second table tells me also 406 : =count(distinct {<[Shipment week]={'202139'}>}[Nr Commande MX])

The following expression tells me 247 : 
=count (distinct {<[COMMANDE]={"=WEEK_SM_REELLE_ZSQ=YEAR_WEEK_REF_TAUX_SERVICE"}>}COMMANDE)

WEEK_SM_REELLE_ZSQ is the week of shipment
YEAR_WEEK_REF_TAUX_SERVICE is the planned shipment week.

If I select the week 39 in my second straight table, then the calculation is updated as follow:

First I don't understand why the result change when I select a week in the straight table.

Second, Qlik makes me crazy as I don't understand why my count distinct expression does not work as expected.

Any idea how to make it work ?

thanks

[stevejoyce]

This set expressoin:  [COMMANDE]={"=WEEK_SM_REELLE_ZSQ=YEAR_WEEK_REF_TAUX_SERVICE"}

is checking if there is a 1:1 match for a COMMANDE between WEEK_SM_REELLE_ZSQ and YEAR_WEEK_REF_TAUX_SERVICE.  It's not doing WEEK_SM_REELLE_ZSQ "in" YEAR_WEEK_REF_TAUX_SERVICE.  So you are dropping out COMMANDEs that have multiple YEAR_WEEK_REF_TAUX_SERVICE.

If you want to do this field = field evaluation, you have to make sure it's at the right granularity.

The easiest thing to do is a rowno() field that is unique for each row.  you can use that in your set expression.

LOAD rowno() as Key,

[Nr Commande MX],
[Planned shipment week],
[Line number],
Qty,
[Qty shipped],
[Shipment week],
[Line shipped on time],
[Line not shipped on time]
FROM excel

;

front-end calc would be

=count (distinct {<[Key]={"=[Planned shipment week]=[Shipment week]"}>}[Nr Commande MX])

That's why your results were changing with your selections.  because your data set is changing and at a COMMANDE grain, you may go from 1:many to 1:1 and it was evaluating true after making filters.

Having said the above for front-end calculation.  If you can, do this matching in the load script and flag records that meet criteria, and use that in your set analysis.  That is ideal:

LOAD

If([Shipment week] = [Planned shipment week], 1)as Date_Match_Flag,

[Nr Commande MX],
[Planned shipment week],
[Line number],
Qty,
[Qty shipped],
[Shipment week],
[Line shipped on time],
[Line not shipped on time]
FROM excel

=count (distinct {<Date_Match_Flag={1}>}[Nr Commande MX])

[patricesalem]

Hi steve

first of all, thanks for your detailed explanation....I'm still learning a lot with Qlik and hope to learn more and more !

I've tried your first solution and the result is perfect, I get the expected 406 orders and no change when I only select the planned shipment week in the table :
=count (distinct {<[Auto_Key]={"=[YEAR_WEEK_REF_TAUX_SERVICE]=[WEEK_SM_REELLE_ZSQ]"}>}[COMMANDE])

I've also found another expression that seems to do the job without the auto_key (but I prefer your solution)

sum (Aggr(distinct if ((sum ([QTE_EXPEDIEE_ZSQ]))>0 and YEAR_WEEK_REF_TAUX_SERVICE=WEEK_SM_REELLE_ZSQ,1,0)
,COMMANDE,WEEK_SM_REELLE_ZSQ,YEAR_WEEK_REF_TAUX_SERVICE))

Nevertheless, I'm trying to use your expression to add a second condition to find out within the 406 orders shipped, how many are shipped on time. In the load script, I have flaged all order lines that are shipped on time (or before the planned week) - the dimension name is POSTE_ON_TIME_ZSQ
I tried both of the following expression and they both return 0:

count (distinct {<[Auto_Key]={"=[YEAR_WEEK_REF_TAUX_SERVICE]=[WEEK_SM_REELLE_ZSQ],POSTE_ON_TIME_ZSQ={'1'}"},
[Auto_Key]={"=POSTE_ON_TIME_ZSQ={'1'}"}>}[COMMANDE])

count (distinct {<[Auto_Key]={"=[YEAR_WEEK_REF_TAUX_SERVICE]=[WEEK_SM_REELLE_ZSQ],POSTE_ON_TIME_ZSQ={'1'}"}>}[COMMANDE])

Maybe, you could help me a bit more to find out the correct expression to have 2 conditions in the distinct clause ?

Big thank

[stevejoyce · Accepted solution]

This would be the syntax for multiple conditions on the same field:

{<[Auto_Key]={"=[YEAR_WEEK_REF_TAUX_SERVICE]=[WEEK_SM_REELLE_ZSQ] and POSTE_ON_TIME_ZSQ = 1"} >}

but this is the way you'd typically do it, which would intersect all field set expressions:

count (distinct {<[Auto_Key]={"=[YEAR_WEEK_REF_TAUX_SERVICE]=[WEEK_SM_REELLE_ZSQ]"}, POSTE_ON_TIME_ZSQ={1}>}[COMMANDE])

[patricesalem]

Thanks for this answer again...I will try later in the day and let you know.

Meanwhile, I have noticed something very weird adding the rowno() as key_test in my load function.
it creates correctly the unique key number, nevertheless, I have two dimensions showing a '?' instead of their respective values (COMMANDE and POSTE):

any idea what is causing that ?

thanks

[stevejoyce]

I do not know what's causing ?, i've never seen it before unless, maybe something else in your data file or script.

[patricesalem]

yes - incredible behaviour.

Reading this blog (https://withdave.com/2018/09/qlik-load-performance-with-recno-and-rowno/), I have tried to replace rowno() by recno(). 
If I do so, no issue, I don't have the ? anymore.
Unfortunatelly, I can't use the recno in my data model as I concatenate table...as the recno is reset for each table, I end up with duplicated unique_id values..

So I have to keep searching. I will let you know if I find something in my script.

Once sorted, I will proceed with the tests of your latest expressions

pat

[patricesalem]

I'm advancing. I have identified the part of my load script causing problems :

I have created a new qvf and only load in it my sales table. Same issue, but if I remove this part of the script :

    text (COMMANDE)&'_'&num(POSTE) as key,

then no issue, both commande & poste dimensions appear correctly

If I put back 

LIB CONNECT TO 'Oracle_MX-ORACLE01 (mailleux_qlikadmin)';

LOAD
	//RecNo() as Unique_ID,
    RowNo() as Unique_ID,
     text (COMMANDE)&'_'&num(POSTE) as key,
    [COMMANDE],
     POSTE,....

 then issue again...incredible.

[patricesalem]

hello

I found the answer for the ? bug: it's related to the use of :

text (COMMANDE)&'_'&num(POSTE) as key,
    [COMMANDE],
     POSTE,

If I do:

 ,text (COMMANDE)&'_'&num(POSTE) as key
	,text([COMMANDE]) as COMMANDE
    ,text([POSTE]) as POSTE

then no more issue ! incredible...this is a Qlik Sense May 21 bug for sure !

I will now carry own with our suggestions for the expression...

[patricesalem]

Hi Stevejoyce

Your solution worked like a charm !

I would like to give you a BIG BIG thank you....you can't imagine how many douzens of hours I've been going round in the last weeks...you gave me THE answer that opened all doors to my service level calculation.

big big thanks !