topic Re: aggr function for current selection in App Development

aggr function for current selection

Mon, 06 Mar 2017 08:13:16 GMT

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 ?

Re: aggr function for current selection

Mon, 06 Mar 2017 08:44:20 GMT

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 09:38:33 GMT

I might be able to offer better help if you are able to share a sample qvf file?

Re: aggr function for current selection

Mon, 06 Mar 2017 10:10:33 GMT

see example

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 10:27:50 GMT

You will have to provide me with some other information as which chart are we looking at? What is the expected output based on the qvw provided?

Re: aggr function for current selection

Mon, 06 Mar 2017 10:33:00 GMT

Look at chart "Data contro data" (the second one)

Expression : Nr.clienti YTD (starting with SUM-AGGR and IFs) : only total is correct

CORRECT DATA : correct data (but this expression is without IFs)

C_Stagioni are current selection

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 10:37:48 GMT

I am still trying to understand this... will respond back when I have something for you.

Best,

Sunny

Re: aggr function for current selection

Mon, 06 Mar 2017 10:41:12 GMT

guess it has something to do with the AGGR function vs. Current Selections

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 11:57:50 GMT

Can you elaborate on the use of the other if statements here? You expect the same numbers as CORRECT DATA even though there are extra if statements?

if (DT_Ordine >= if (isnull(dVon1) or dVon1 = '', DT_Ordine, dVon1) AND

DT_Ordine <= if (isnull(dBis1) or dBis1 = '', DT_Ordine, dBis1),

I guess I am wondering what exactly are you looking to do here?

Re: aggr function for current selection

Mon, 06 Mar 2017 12:05:17 GMT

In my example - in order to check results - I am assuming dVon and dBis in a way that this result is same as CORRECT DATA

Once Expression works I will restrict these Variables to get other results

Paolo Prinoth

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

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 12:19:16 GMT

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

Re: aggr function for current selection

Mon, 06 Mar 2017 12:46:42 GMT

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

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 12:50:49 GMT

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

Re: aggr function for current selection

Mon, 06 Mar 2017 13:04:42 GMT

[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:

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 13:06:37 GMT

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

Re: aggr function for current selection

Mon, 06 Mar 2017 13:07:26 GMT

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

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 13:38:58 GMT

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

Re: aggr function for current selection

Mon, 06 Mar 2017 13:41:02 GMT

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

Re: aggr function for current selection

sunny_talwar — Mon, 06 Mar 2017 14:04:59 GMT

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

Re: aggr function for current selection

Mon, 06 Mar 2017 14:09:30 GMT

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