Solved: count distinct if - Qlik Community

Report Inappropriate Content · ‎2017-03-02

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)

vishsaggi · ‎2017-03-03

Ok. I got it. Do the below changes.

1. Your field C_Stagione has value like R14, T25D .... the letters are all capitals. So in you inputbox you have give exactly the same case. You have been giving r14, t25d, Qlikview thinks R14 and r14 as two different values. So in your input box for sSaison1 ....sSaison2....sSaison4 etc add the letters with Capitals like R14 T25D.

2. In your date variables you have been using date format like 1.1.00 or 15.3.17 etc...Just go to settings tab on the top -> Variables Overview (you can see the window lilke below) -> For the date variables like in below screenshot change the dates to DD.MM.YYYY format. LIke dVon1 = 1.1.2000

dbis1 = 15.3.2017.....etc. So it should be in the same format as your DT_ordine field. Now after that try my expression for Nr.Clienti YTD.

View solution in original post

vishsaggi · ‎2017-03-02

What is the issue here?

Report Inappropriate Content · ‎2017-03-02

?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

vishsaggi · ‎2017-03-02

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

Report Inappropriate Content · ‎2017-03-02

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

Report Inappropriate Content · ‎2017-03-02

?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

vishsaggi · ‎2017-03-02

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

A

B

C

];

Concatenate

LOAD * INLINE [

Saison2

A

D

];

NoConcatenate

LOAD *, AutoNumber(Saison1&Saison2) AS Key

Resident TAB;

DROP TABLE TAB;

In TextBox use this expression:

= Count(DISTINCT Key)

Report Inappropriate Content · ‎2017-03-02

?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

vishsaggi · ‎2017-03-02

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

vishsaggi · ‎2017-03-02

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 ??

count distinct if

Related Topics