count distinct if

[#1]

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]

Did you look into the attachment i have attached?

[vishsaggi]

Can you share your app where you are implementing this expression?

[#4]

Which attachment ?

[#5]

Can you send it to me directly not via Community Forum ?

[vishsaggi · Accepted solution]

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.

[#7]

PERFECT. CONGRATS.

P.S. Is there a reward system in the Qlik Community ?

[vishsaggi]

If you find any of my reply threads as correct you can mark them as correct and other you find helpful you can do that too. So, you can close the thread by marking the right thread as correct.

Tips here:

Qlik Community Tip: Marking Replies as Correct or Helpful

[#9]

I got maybe where the error is: I do not need the total number of C_Cliente who bought in both seasons sSaison1 AND in sSaison2, but the number of those who bought OR in sSaison1 OR sSaison OR in both

[#10]

The order of the IFs is changing the result !