topic Nested Aggregations and Drill Down Groups in QlikView

Nested Aggregations and Drill Down Groups

matthewjbryant — Tue, 29 Jul 2014 14:55:11 GMT

Hi All,

Is it possible to perform different aggregations at different stages of a drill down group? I am working on a list of parts where the average price needs to be shown at the part level and the sum of the average price needs to be shown at the site level (the site that manufactured the parts. You can total an average at the bottom of the table when viewing the part level, but when you zoomed out to the site level I can only see how to show the average price of all parts manufactured on that site.

The function I am using is basically avg(price). sum(avg(price)) breaks the column so I'm guessing Qlikview doesn't like nested aggregations. Is there a way around this?

Required Result:

Part Level:

Part	Avg Price
A	2
B	1
Total	3

Site Level:

Site	Total Price
Lon	3
Bham	3

(A & B made in Lon, C made in Bham)

Re: Nested Aggregations and Drill Down Groups

michael_maeuser — Tue, 29 Jul 2014 15:03:43 GMT

i don´t understand the issue exactly, but can´t you solve it with a pivot table?

basically i would say aggr is the expression needed

Re: Nested Aggregations and Drill Down Groups

male_carrasco — Tue, 29 Jul 2014 15:12:12 GMT

Hi Matthew,

I don´t understand the issue exactly, maybe you need:

1- Create a variable =GetCurrentField("Name_drill _down _group")

2_sum(aggr(avg(price),$(variable))

ale.-

Re: Nested Aggregations and Drill Down Groups

matthewjbryant — Tue, 29 Jul 2014 15:12:23 GMT

Just using a pivot will not solve the problem. Can you help me to understand how the aggr function could assist me here?

Re: Nested Aggregations and Drill Down Groups

matthewjbryant — Wed, 30 Jul 2014 07:47:21 GMT

It doesn't work sadly. This gives me correct data at the Part level view (the average of each part and then the sum of all), but at a Site level view this shows just the average (not what was displayed in the total column in the Part section of the drill-down).

I have got close by using sum(aggr(avg(Price),Part)), but this gives me odd results about 5% of the time. Sometimes the value display against the Site bears no resemblance to the data found by drilling down to Part. I can't work out why this is.

Re: Nested Aggregations and Drill Down Groups

jonathandienst — Wed, 30 Jul 2014 07:51:29 GMT

Matthew

When you use aggr() in a chart, the dimensions in the aggr() statement need to include all the table/chart dimensions, so you might need something like:

sum(aggr(avg(Price),Part,Site)),

(assuming Part and Site are the table dimensions)

HTH

Jonathan

Re: Nested Aggregations and Drill Down Groups

matthewjbryant — Thu, 31 Jul 2014 08:02:11 GMT

Further analysis has proved that this doesn't solve the problem, sadly. When I compare sum(aggr(avg(Price),Part,Site)) at the Part level of the drill down with avg(Price) on a table with just a Part dimension the values do not add up.

I need the price averaged at the Part level and that average summed at a Site level, but the aggr function is not averaging the values correctly with respect to Part as it stands.

The problem (or at least part of the problem) is that the aggr function is turning null values into 0. Why does it do this and is there a way of stopping it?

Re: Nested Aggregations and Drill Down Groups

male_carrasco — Thu, 31 Jul 2014 11:56:13 GMT

Could you provide some data example in a qvw?

Ale