aggr function for current selection

[#1]

We have following Expression

= 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  ) )

sSaison1, sSaison2, dVon1, dVon2, dBis1, dBis2 are in an INPUTBOX

The (total) result is only correct for current selections.

For example in any case we need to have current selection for C_Stagione (which is the same as sSaison..)

The results work fine with following Expression

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

but this expression does not consider further conditions (dVon.., dBis..)

Any idea how to solve ?

[sunny_talwar]

I am going to repeat this for the 3rd time now.... Yes, it is a beast and I don't think you should use it.... I think CORRECT DATA expression can be modified to add the from and to conditions. I am not sure why you don't want to use that? If you think you want to use that, I might be able to help you out... else you will have to live with the beast

[#3]

Sorry for the misunderstanding

I would love to use the "Correct Data" Expression : that would be the perfect solution

Il giorno 06 mar 2017, alle ore 15:14, Sunny Talwar <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto:

[sunny_talwar]

So in order to this, I would need you to give me 2-3 desired outputs based on different inputs in dVon and dBis.

[#5]

Can you use the two screenshots I have sent earlier ?

[sunny_talwar]

Screenshots are way to small to see anything.... Would you be able to share the numbers as text or as Excel file based on inputs?

[sunny_talwar · Accepted solution]

Try this expression:

=Count({

  <C_Stagione = {'$(sSaison1)'}, DT_Ordine = {"$(='>=' & TimeStamp(dVon1) & '<=' & TimeStamp(dBis1))"}> +

  <C_Stagione = {'$(sSaison2)'}, DT_Ordine = {"$(='>=' & TimeStamp(dVon2) & '<=' & TimeStamp(dBis2))"}>

  } distinct C_Cliente)

[#8]

Had to leave

Will come back tomorrow

Tks

Paolo Prinoth

Il giorno 06 mar 2017, alle ore 17:12, Sunny Talwar <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto:

[#9]

your expression seems to work

I will do further checks, but TKS and CONGRATS

[#10]

Thank you - it works - but sometimes we get a difference of one

The very first expression we used is finishing with a zero 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))

Your expression

=Count({

  <C_Stagione = {'$(sSaison1)'}, DT_Ordine = {"$(='>=' & TimeStamp(dVon1) & '<=' & TimeStamp(dBis1))"}> +

  <C_Stagione = {'$(sSaison2)'}, DT_Ordine = {"$(='>=' & TimeStamp(dVon2) & '<=' & TimeStamp(dBis2))"}>

  } distinct C_Cliente)

is not stating the "0"

What is the difference ?