Aggr and Set analysis not working properly

[thymenkristen]

Dear Qlik community,

Currently I'm working on a project with the purpose of segment the customer base.

The first thing i did was to segment the customer base into groups based on the number of departments the customer bought from and the turnover that was realised for each customer. See following statement:

=Aggr(

if(sum([Regelbedrag Netto]) <= 0 and aggr(count( DISTINCT Afdeling), Debiteur_Key) = 0, 'Prospect',

If(Sum([Regelbedrag Netto]) <= 4500 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) <=2, 'Dp-klant',

If(Sum([Regelbedrag Netto]) <= 4500 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) >2, 'D-klant',

If(Sum([Regelbedrag Netto]) <= 25000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) <=2, 'Cp-klant',

If(Sum([Regelbedrag Netto]) <= 25000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) >2, 'C-klant',

If(Sum([Regelbedrag Netto]) <= 85000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) <=2, 'Bp-klant',

If(Sum([Regelbedrag Netto]) <= 85000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) >2, 'B-klant',

If(Sum([Regelbedrag Netto]) > 85000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) <=2, 'Ap-klant',

If(Sum([Regelbedrag Netto]) > 85000 and aggr(Count( DISTINCT Afdeling),Debiteur_Key) >2, 'A-klant',
)))))))))

, Debiteur_Key)

Leading to the above result.

Since then we have released a potency calculation at customer level, and I want to include this in the coding that we assign to each customer. So, in addition to the realized turnover, the number of departments where purchased, we also want to include potential in customer coding.

First I exported and re-imported all relationships including (the above) code based on the relation_key. In this way I made the above coding static so that I can work with a set analysis. This is how I came to the following:

=Aggr(

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) <= 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) > 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'DC-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) <= 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) <= 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'DD-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) > 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) > 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'DA-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) > 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) <= 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'DB-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) <= 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) > 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'CC-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) <= 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) <=97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'CD-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) > 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) > 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'CA-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) > 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) <= 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'CB-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>} [Regelbedrag Netto]) <= 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) > 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'BC-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) <= 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) <=73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'BD-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) > 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) > 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'BA-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) > 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) <= 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'BB-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) <= 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) > 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'AC-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) <= 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) <= 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'AD-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) > 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) > 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'AA-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) > 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) <= 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'AB-klant',
))))))))))))))))

, Debiteur_Key)

In my experience, this should lead to the same number of relationships as the above code applies. The only thing I expect to change is that each coding is further subdivided into other codings. However, this is the result:

Can someone help me and maybe tell me where things go wrong? Thanks in advance!

[sunny_talwar]

What if something didn't meet the condition... you don't have a last false condition here

=Aggr(

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) <= 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) > 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'DC-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) <= 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) <= 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'DD-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) > 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) > 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'DA-klant',

If(Sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Regelbedrag Netto]) > 2250 and sum({<[Codering 2018]={'D-klant', 'Dp-klant'}>}[Potentie totaal]) <= 166500 and aggr(Count({<[Codering 2018]={'D-klant', 'Dp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'DB-klant',


If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) <= 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) > 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'CC-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) <= 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) <=97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'CD-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) > 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) > 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'CA-klant',

If(Sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Regelbedrag Netto]) > 12500 and sum({<[Codering 2018]={'C-klant', 'Cp-klant'}>}[Potentie totaal]) <= 97750 and aggr(Count({<[Codering 2018]={'C-klant', 'Cp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'CB-klant',


If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>} [Regelbedrag Netto]) <= 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) > 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'BC-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) <= 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) <=73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'BD-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) > 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) > 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'BA-klant',

If(Sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Regelbedrag Netto]) > 42000 and sum({<[Codering 2018]={'B-klant', 'Bp-klant'}>}[Potentie totaal]) <= 73500 and aggr(Count({<[Codering 2018]={'B-klant', 'Bp-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'BB-klant',


If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) <= 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) > 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'AC-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) <= 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) <= 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) <=2, 'AD-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) > 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) > 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'AA-klant',

If(Sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Regelbedrag Netto]) > 312500 and sum({<[Codering 2018]={'A-klant', 'Ap-klant'}>}[Potentie totaal]) <= 132500 and aggr(Count({<[Codering 2018]={'A-klant', 'Ap-klant'}>} DISTINCT Afdeling),Debiteur_Key) >2, 'AB-klant'
, 'NoneOfTheAbove'
))))))))))))))))


, Debiteur_Key)

[marcus_sommer · Accepted solution]

I assume that your clustering is rather static and could be therefore (partly) transferred into the script. This would make the things in the UI much easier and also the performance would be significantly increase.

Beside this also within the UI could be such approach simplified, maybe with something like:

aggr(
pick(match(-1, sum([Regelbedrag Netto]) <= 0, Sum([Regelbedrag Netto]) <= 4500, ...),
   'Prospect', 'D', ...) &
pick(match(-1, aggr(count( DISTINCT Afdeling), Debiteur_Key) = 0, aggr(count( DISTINCT Afdeling), Debiteur_Key) <= 2, ...),
    '', 'p', ...) & '-klant'), Debiteur_Key)

It's here not complete and you might need a further adjustment / different logic for Prospect but I think it's easier and more performant as the multiple if-loops. Further if there is no special need for those different bucket-sizes you could it simplify more with class().

And the more you simplify your logic the easier would it be to detect any mistakes within the expressions and/or the logic behind them.

- Marcus