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!