topic Aggr() function in QlikView

Aggr() function

Frank_Hartmann — Wed, 12 Feb 2014 17:11:52 GMT

Hi QV community,

i have the following expression in a textbox:

((if(sum({$ < [MonthDiff]={[$(vAktuellerMonat)]} >}[Nutzung kummulliert])>=0.9*((sum({$ < [MonthDiff]={[$(vZeitraum12Monate)]} >}[Nutzung kummulliert_1]) +

sum({$ < [MonthDiff]={[$(vZeitraum11Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum10Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum9Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum8Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum7Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum6Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum5Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum4Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum3Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vZeitraum2Monate)]} >}[Nutzung kummulliert_1])+

sum({$ < [MonthDiff]={[$(vAktuellerMonat)]} >}[Nutzung kummulliert_1]))/12),1,0)) +

sum({$<[MonthDiff]={[$(vAktuellerMonat)]}>}(if(Themenzugriffe_KPI>=0.9*[gl. DS],1,0)))+

sum({$<[MonthDiff]={[$(vAktuellerMonat)]}>}UC1_KPI)+

sum({$<[MonthDiff]={[$(vAktuellerMonat)]}>}UC2_KPI))/4

i replaced this expression by a variable vSumKum

$(vSumKum) gives me the value 0 or 0.25 or.0.5 or 0.75 or 1 per month in TEXTBOX depending on the status of the four sum functions above.

Now i want to Aggregate these values over the last 12 month.

I tried something like that:

Aggr(sum({$ < [MonthDiff]={[$(vZeitraum12Monate)]} >}$(vSumKum)),MonthDiff) but this doesnt work!

Somebody any ideas how this formula has to look like?

Re: Aggr() function

Wed, 12 Feb 2014 17:46:39 GMT

Franck,

The problem is that the set analysis will be evaluated once only in the chart (and I assume the AGGR also)

Fabrice

Re: Aggr() function

stevedark — Wed, 12 Feb 2014 18:13:26 GMT

Hi Frank,

Without getting my head around all of your expression I couldn't say for sure.

I notice that you don't have another aggregation around your Aggr - which is generally the point of using AGGR, this would give you:

=sum(Aggr(sum({$ < [MonthDiff]={[$(vZeitraum12Monate)]} >}$(vSumKum)),MonthDiff))

That would then give you the result for each month and then tot up the totals.

Also, often you need to repeat your set analysis in the outer statement as well as the functions within it:

=sum({$ < [MonthDiff]={[$(vZeitraum12Monate)]} >} Aggr(sum({$ < [MonthDiff]={[$(vZeitraum12Monate)]} >}$(vSumKum)),MonthDiff))

Given that you have a sum both inside and outside the AGGR now I may be missing the point of what you are trying to achieve, but hopefully this will point you in the right direction.

- Steve

Re: Aggr() function

hschultz — Fri, 14 Feb 2014 06:18:48 GMT

Here are 3 things that might help

Firstly, using Aggr over something that you have used set analysis over the same dimensions usually causes problems. -You are selecting a specific MonthDiff, but then what to Aggr over all of them. In this case you have to use another sum around the Aggr to get the total of all MonthDiff.

Then, you could use a range in a single set Analysis. Something like this:

sum({<[MonthDiff] = {"<=$(=Max(YourMonth))>=$(=Max(YourMonth)-11)"}>} [Nutzung kummulliert_1])

I see you calculate all months and /12, but using the above set analysis you can use AVG as the prefix:

avg({<[MonthDiff] = {"<=$(=Max(YourMonth))>=$(=Max(YourMonth)-11)"}>}[Nutzung kummulliert_1])

Not sure if it solves your problem, but hopefully it can help somewhat