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]

When i count the C_Cliente for the Value A17A i am getting only 2 C_Cliente values ?

[#3]

For the old file this would be correct

Paolo Socrep

p.prinoth@socrep.it<mailto:p.prinoth@socrep.it>

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

[vishsaggi]

Did you try using the same count distinct like this for your nr.Client YTD

= count ( {< C_Stagione = {'$(sSaison1)','$(sSaison2)'} >} distinct C_Cliente)

[#5]

Yes, but this is not taking into account that sSaison1 is further conditioned by dVon1 and dBis1 and sSaison2 by dVon2 and dBis2

It is all about this further conditions

Paolo Socrep

p.prinoth@socrep.it<mailto:p.prinoth@socrep.it>

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

[vishsaggi]

Now i got what you mean, i have 13 dups coming from both the conditions, so we need to distinct that and get 32 as the count. Hmmm, not a straight forward option, i am trying but let us see how we can do it. Need little time on this.

[#7]

This it is.

If I take the whole period then = count ( {< C_Stagione = {'$(sSaison1)','$(sSaison2)'} >} distinct C_Cliente) works, but I need also to compare periods defined by dVonx until dBisx (Von German for FROM, Bis German for UNTIL)

Paolo Socrep

p.prinoth@socrep.it<mailto:p.prinoth@socrep.it>

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

[vishsaggi]

Try this? Just copy paste the below expression with sSaison1 as R14 and sSaison2 as T25D.

Nr.Clienti YTD expression copy past the below?

= Sum(Aggr(Count(DISTINCT IF( C_Stagione = '$(sSaison1)',

                     if (Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY') >= if (isnull(dVon1) or dVon1 = '',

                     Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY'), dVon1) AND

                     Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY') <= if (isnull(dBis1) or dBis1 = '',

                     Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY'), dBis1), C_Cliente),

                   IF(C_Stagione = '$(sSaison2)',

                      if (Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY') >= if (isnull(dVon2) or dVon2 = '',

                      Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY'), dVon2) AND

                      Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY') <= if (isnull(dBis2) or dBis2 = '',

                      Date(Date#(DT_Ordine, 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY'), dBis2), C_Cliente)))), C_Cliente))

[#9]

Unfortunately it is not working (the result is “0”, not “-“)

1. Why the date format ? And do I have to consider this in the InputBox?

2. Shouldn’t (sSaison1 and dVon1 and bBis1) as a whole be separated from( sSaison2 and dVon2 and dBis2) ? I changed the order of the 4 IF’s and result was again “0”

[vishsaggi]

That date conversion i just did coz i thought we are checking the condition with different date formats, but it still works even if we don't use that format. So i have removed those formatting functions and used below. Check the app attached.

= Sum(

          Aggr(

                  Count(DISTINCT IF(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),

                                IF(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)))), C_Cliente

              )

    )

[#11]

No error message on date format, but still result “0” instead of 32

Maybe as previous message something to do with sSaison1 + dVon1 + dBis1 to be “separated” from sSaison2 + dVon2 + dBis2