topic Re: count distinct if in QlikView

count distinct if

Thu, 02 Mar 2017 10:34:57 GMT

I do not need the sum of the two Seasons, but a distinct count over the two seasons and with if

count (distinct {$<C_Stagione = {$(#sSaison1)}>} if (DT_Ordine >= if (isnull(dVon1) or dVon1 = '', DT_Ordine, dVon1) AND

DT_Ordine <= if (isnull(dBis1) or dBis1 = '', DT_Ordine, dBis1), C_Cliente, 0))

+ count (distinct {$<C_Stagione = {$(#sSaison2)}>} if (DT_Ordine >= if (isnull(dVon2) or dVon2 = '', DT_Ordine, dVon2) AND

DT_Ordine <= if (isnull(dBis2) or dBis2 = '', DT_Ordine, dBis2), C_Cliente, 0))

If there would not be IF I would solve it like this

count (distinct {$<C_Stagione = {$(#sSaison3),$(#sSaison4)}>} C_Cliente)

Re: count distinct if

vishsaggi — Thu, 02 Mar 2017 16:51:18 GMT

What is the issue here?

Re: count distinct if

Thu, 02 Mar 2017 17:44:58 GMT

?If I use my string I get the result of the count of Saison1 + the result of Saison2

What I need is the total count of the combined Saison1 and Saison2 (in Saison 2 there may be the same value as in Saison1) both if a different condition

SOCREP SRL

Paolo Prinoth

Re: count distinct if

vishsaggi — Thu, 02 Mar 2017 17:54:02 GMT

Can you share a sample app with expected output, i am quite not sure if i have understood your issue? Sorry.

Re: count distinct if

Thu, 02 Mar 2017 17:54:34 GMT

P.S. There is a small error in the string without IF - it should read

count (distinct {$} C_Cliente)

Sorry for the error

SOCREP SRL

Paolo Prinoth

Re: count distinct if

Thu, 02 Mar 2017 17:59:22 GMT

?Example

Saison1, customer A,A,B,C

Saison2, customer A,D,D

With my string I get result of 5 (A,B,C of Saison1 + A,D of Saison2)

The correct result would be 4 (A,B,C,D)

Saison1 and Saison2 have two different conditions

SOCREP SRL

Paolo Prinoth

Re: count distinct if

vishsaggi — Thu, 02 Mar 2017 19:51:40 GMT

I am not sure how your data looks based on your above data create a key for the two fields Saison1 and Saison2 and count the key with distinct.

TAB:

LOAD * INLINE [

Saison1

];

Concatenate

LOAD * INLINE [

Saison2

];

NoConcatenate

LOAD *, AutoNumber(Saison1&Saison2) AS Key

Resident TAB;

DROP TABLE TAB;

In TextBox use this expression:

= Count(DISTINCT Key)

Re: count distinct if

Thu, 02 Mar 2017 21:11:24 GMT

?Thank you.

Within a CHART BOX

C_Stagione and C_Cliente are Dimension; sSaison.. + dVon... + dBis... are Variables in an INPUT BOX; and "my" string is the EXPRESSION

SOCREP SRL

Paolo Prinoth

Re: count distinct if

vishsaggi — Thu, 02 Mar 2017 21:15:22 GMT

Can you share/upload your app you are working on? It would be easy to look at it and give you correct solution. ? Check this:

Preparing examples for Upload - Reduction and Data Scrambling

Re: count distinct if

vishsaggi — Thu, 02 Mar 2017 21:46:49 GMT

Can you explain where i can see the below expression in your attached file or a screenshot where you want to see and where is your expression in that chart? I quite did not get you mean by Chart Box? You mean chart title or ?

C_Stagione and C_Cliente are Dimension; sSaison.. + dVon... + dBis... are Variables in an INPUT BOX; and "my" string is the EXPRESSION ??

Re: count distinct if

Fri, 03 Mar 2017 06:20:26 GMT

Tab Row : Data contro data

Chart: Confronto valori progressivi ……

Expression: Nr. clienti YTD

Il giorno 02 mar 2017, alle ore 22:47, Vishwarath Nagaraju <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto:

Re: count distinct if

Fri, 03 Mar 2017 13:44:29 GMT

Re: count distinct if

Fri, 03 Mar 2017 14:13:53 GMT

Re: count distinct if

vishsaggi — Fri, 03 Mar 2017 14:27:12 GMT

What is your expected value you have to see in that nr.ClientYTD field?

Re: count distinct if

Fri, 03 Mar 2017 14:33:32 GMT

only R14 would be 20 - only T25D would be 25 : therefore it shows 45 which is wrong because some are double

Re: count distinct if

Fri, 03 Mar 2017 14:36:41 GMT

Looking at this list you may understand what I need

THANK YOU

Re: count distinct if

Fri, 03 Mar 2017 14:40:45 GMT

IN "Nr. clienti PY" I solved it with

count (distinct {$<C_Stagione = {$(#sSaison3),$(#sSaison4)}>} C_Cliente)

But i"nr.ClientYTD" has no conditions

Re: count distinct if

Fri, 03 Mar 2017 14:43:49 GMT

* but "Nr.Clienti YTD" HAS conditions dVonx and dBisx

Re: count distinct if

vishsaggi — Fri, 03 Mar 2017 14:45:24 GMT

From which field you are getting this R14 and T25D ? I assume these values should be there in C_Stagione field? But i see only values with A and S numbers? Are these driven based on DT_Ordine?

Re: count distinct if

Fri, 03 Mar 2017 14:50:30 GMT

I have attached a new file (scrambled). Yes, they are C_Stagione. If you enter them in Input Box (see screenshot) the result is 45

P.S. As you see I am bit beginner

Il giorno 03 mar 2017, alle ore 15:45, Vishwarath Nagaraju <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto: