Nested Aggregations and Drill Down Groups

[matthewjbryant]

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)

[michael_maeuser]

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

[male_carrasco]

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.-

[matthewjbryant]

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

[matthewjbryant]

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.

[jonathandienst]

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

[matthewjbryant]

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?

[male_carrasco]

Could you provide some data example in a qvw?

Ale