Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hcavanhoogdalem
New Contributor III

AGGR function with multiple dimensions

Hi all,

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

Consider the following pivot-table:

1.png

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:

2.png

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:

3.png

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:

4.png

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

Tags (2)
1 Solution

Accepted Solutions

Re: AGGR function with multiple dimensions

Are you looking to get this

183/1516

1/1430

.

.

and so on?

May be try this:

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

3 Replies

Re: AGGR function with multiple dimensions

Are you looking to get this

183/1516

1/1430

.

.

and so on?

May be try this:

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

hcavanhoogdalem
New Contributor III

Re: AGGR function with multiple dimensions

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

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

Community Browser