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 think it might be an issue with the dimensions you are using in your Aggr() function..... You can try like this

=If(Dimensionality() <= 1,

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_Marchio)),

If(Dimensionality() = 2,

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_Marchio, D_GruppoArticolo)),

If(Dimensionality() = 3,

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_Marchio, D_GruppoArticolo, D_CategoriaArticolo)),

If(Dimensionality() = 4,

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_Marchio, D_GruppoArticolo, D_CategoriaArticolo, D_Articolo)),

If(Dimensionality() = 5,

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_Marchio, D_GruppoArticolo, D_CategoriaArticolo, D_Articolo, D_VarLivello1)),

If(Dimensionality() = 3,

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_Marchio, D_GruppoArticolo, D_CategoriaArticolo, D_Articolo, D_VarLivello1, D_VarLivello2))))))))

But we should rather try to fix the CORRECT DATA expression to incorporate the if statement you have in the Aggr() function. I just need to know what the expected output is when those if statement come in action and we might be able to work with a set analysis rather than this beast.....

[#3]

Well done. I made several checks and it seems the results are correct (if I do not change/add/remove dimensions).

We will need both Expressions

1) The one you just did = considering counting customers with orders from date dVon to date dBis – seems working (see above)

2) The one I just called CORRECT DATA = with no restrictions, ergo all customers of that season (C_Stagione) – which always worked

Any chance to simplify also to be used in other charts with other dimensions (copy/paste) ?

[sunny_talwar]

In its current form, it would be rather difficult to simplify.... the only thing you can do is to replace If statement with Pick statement, but the length of the expression will stay this long.... On the other hand if we introduce the orders from date dVon to date dBis in CORRECT DATA using set analysis, we might be able to get rid of Aggr (which I totally think is doable because we are using static dates here) and make the expression just 1 or 2 lines.

If you can give me 2-3 expected output based on different inputs of dVon and dBis, we might be able to get it to work.

Best,

Sunny

[#5]

[cid:5AD3D8C6-4DBF-40D5-AD3E-F49E8E00755F]

If I add a new dimension (D_Agente) then the result is not correct

Paolo Socrep

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

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

[sunny_talwar]

I agree it isn't and that is why I want you to help fix the expression using set analysis my friend

[#7]

why is If(Dimensionality() = 3, stated twice ?

[sunny_talwar]

That is a typo... the last one should be 6

[#9]

On another note : the original Expression compared only sSeason1 with sSeason2 - I added sSeason3 and sSeason4 in order to compare the sum of sSeason1 and sSeason2 with sSeason3 and sSeason4 because I was not able to put in the INPUTBOX two variables

This to explain why we came to this expression

[sunny_talwar]

So it everything resolved now? I am not sure where we stand

[#11]

Your “beast” ☺ is working, but

· Difficult to adapt to other charts with other dimensions

· Sum is not correct i.e. the sum shown is the arithmetical sum, not the sum without duplicates