Aggr function in script

[thymenkristen]

Hi all,

In my front-end development i used several times the following aggr-function. However, this is making my Qlikview app very slow. Therefore, I want to cover for this in the script. But I'm pretty new to Qlikview and scripting, so I don't really understand where to start (see function below).

I have to:

- Cover the sum part in the script per Debiteur_key (Regelbedrag Netto, Debiteur_Key)

- Cover the set-analysis part in the script, I only want to sum information looking 12 months back.

- Cover the count Distinct part in the script, Debiteur_Key

Does anyone have any suggestions?

Thaks in advance!

=Aggr(

if((sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 0 and aggr(count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling), Debiteur_Key) = 0, 'Prospect',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 4500 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) <=2, 'Dp-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 4500 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) >2, 'D-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 25000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) <=2, 'Cp-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 25000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) >2, 'C-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 85000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) <=2, 'Bp-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) <= 85000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) >2, 'B-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) > 85000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) <=2, 'Ap-klant',

If((Sum({$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}[Regelbedrag Netto])) > 85000 and aggr(Count({$<[Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"} >} DISTINCT Afdeling),Debiteur_Key) >2, 'A-klant',
)))))))))

, Debiteur_Key)

[marcus_sommer · Accepted solution]

There are various noticeable things, for example the max() around of today() which isn't useful in any way. Further I'm not sure if you really need the inner aggr() for the count because the belonging dimension is the same like in the outer aggr() - therefore I assume they isn't mandatory necessary for the calculation.

Beside this I could imagine that not all calculations within your expression are needed to get the wanted result because they have some redundancy.

With the above I don't want to say that's not possible to transfer this calculation to the script or that's not sensible to do it. Rather the opposite ... but it might not very easy if not all fields belong to one table because then you might need multiple steps to prepare an appropriate table to be able to apply your wanted aggregation. The datamodel itself is very important from a performance point of view and should be developed in the direction of a star-scheme or even a big flat-table if there are any performance issues in the UI.

One step which is quite easy to solve within the script is to create a flag for:

{$< [Faktuur Datum]={">=$(=Date(AddMonths(Max(Today()),-12), 'DD-MM-YYYY'))"}>}

for example with:

-([Faktuur Datum] >= AddMonths(Today(),-12)) as Flag

This flag could be used within a set analysis or just as multiplicator to calculations, like:

sum([Regelbedrag Netto]) * Flag

Within your expression it won't be a big improvement to the performance but it will simplify the approach and enhance the readability. Therefore I suggest you take a look on my previous recommendation:

Aggr-and-Set-analysis-not-working-properly

whereby it would be probably helpful to develop such expression step by step and if the parts work you merge them again.

- Marcus