topic AGGR function with multiple dimensions in QlikView

AGGR function with multiple dimensions

Anonymous — Wed, 05 Oct 2016 10:39:57 GMT

Hi all,

There is something I just don't get about a certain AGGR expression I am using.

Consider the following pivot-table:

I use the COUNT(EMPLID) expression to get a simple line count.

I use 3 dimensions, Onderwerp SchooljaarExamen and CijferCategorie.

It works perfectly.

I am however more interested in the relative amount per Onderwerp/Schooljaar so I need the total per schooljaar per onderwerp

Like this:

As you can see it totals the amount per year nicely per onderwerp.

Ok, the standard solution is to use an aggr function to aggregate the same result.

Like this:

Which yield exactly the same table.

Almost there I thought, now I just have to reintroduce the CijferCategorie-dimension, and it should (imho) yield the same totals. If that works I can use the AGGR expression in the first table.

But then I get this:

The totals pop up in totally unpredictable fields. Dimensions partially disappear...

Even if I change the expression to =AGGR(COUNT( EMPLID), SchooljaarExamen, Cijfercategorie, Onderwerp) I get the same result.

I just don't get what's happening here.

The questions:

1. Can I get it to work using this train of thought?

2. Or is there perhaps an easier way to change the numbers in the first table to relative per year/cijfercategorie

Thanks

Herbert

Re: AGGR function with multiple dimensions

sunny_talwar — Wed, 05 Oct 2016 16:29:14 GMT

Are you looking to get this

183/1516

1/1430

and so on?

May be try this:

Count(EMPLID)/Count(TOTAL <Onderwerp, SchooljaarExamen> EMPLID)

Re: AGGR function with multiple dimensions

Anonymous — Thu, 06 Oct 2016 10:28:57 GMT

Hi Sunny,

Yes, that is exactly what I want, thanks a lot (again )

But I'm still interested to understand why the aggr does not do the exact same thing. I can't understand why it behaves so strangely when I reintroduce the CijferCategorie dimension in the 4th picture.

Kind regards, Herbert

Re: AGGR function with multiple dimensions

sunny_talwar — Thu, 06 Oct 2016 17:12:36 GMT

We can force Aggr() to do it, but why use a complicated option when you can simplify things