Nesting aggregations: SUM(AVG())?

[#1]

I can't get my head around how to nest different types of aggregations. I basically need the SUM() of an AVG(). Once I figured out how to nest aggregations I'm sure I find a way to present the resulting data in different diagram types. Let me provide with you with some example data and some SQL describing what I want to do:

My warehouse dumps every day the quantity for each SKU in the warehouse. The Result looks something like below. The original has a few more interesting rows like monetary value per row etc. but you get an idea.

date | quantity | sku
------------+----------+----------
 2006-07-16 | 5316 | sku00
 2006-07-16 | 1875 | sku05
 2006-07-16 | 368 | sku10
 2006-07-16 | 917 | sku20
 2006-07-16 | 10928 | sku50
 2006-07-17 | 5316 | sku00
 2006-07-17 | 1875 | sku05
 2006-07-17 | 368 | sku10
 2006-07-17 | 917 | sku20
 2006-07-17 | 10928 | sku50
 2006-07-18 | 3487 | sku00
 2006-07-18 | 1871 | sku05
 2006-07-18 | 368 | sku10
 2006-07-18 | 914 | sku20
 2006-07-18 | 10753 | sku50
 2006-07-21 | 4211 | sku00
 2006-07-21 | 1871 | sku05
 2006-07-21 | 368 | sku10
 2006-07-21 | 906 | sku20
 2006-07-21 | 10694 | sku50
 2006-07-22 | 4211 | sku00
 2006-07-22 | 1859 | sku05
 2006-07-22 | 368 | sku10
 2006-07-22 | 908 | sku20
 2006-07-22 | 10694 | sku50
 ...

I'm fine with reading the SQL data into qlickview. I also can display it and use drill down groups for selecting by year, month, day and the like.

But to make sense of the data I want to know the average quantity for any SKU. In SQL land I would do something like this to get average quantity by month and sku:

SELECT date(date_trunc('month', date)) as month, avg(quantity), sku FROM footab GROUP BY sku, month;

month | avg | sku
------------+-------+----------
 2006-07-01 | 4266 | sku00
 2006-07-01 | 1859 | sku05
 2006-07-01 | 368 | sku10
 2006-07-01 | 905 | sku20
 2006-07-01 | 10738 | sku50
 2006-08-01 | 2745 | sku00
 2006-08-01 | 1608 | sku05
 2006-08-01 | 368 | sku10
 2006-08-01 | 1 | sku12
 2006-08-01 | 2 | sku13
 2006-08-01 | 797 | sku20
 2006-08-01 | 10568 | sku50
 2006-08-01 | 2 | sku90
 2006-09-01 | 420 | sku00
 ...

But in addition I want to know the TOTAL quantity per month. This would obyiously be more interesting for monetary value or storage volume. In SQL I would do this with a subquery:

SELECT month, sum(avg) FROM (
 SELECT date(date_trunc('month', date)) as month, avg(quantity)::integer, sku FROM footab GROUP BY sku
) group by month;

month | sum
------------+-------
 2006-07-01 | 18136
 2006-08-01 | 19478
 2006-09-01 | 15860
 ...

So basically I do an SUM(AVG(quantty) GROUP BY sku, month) GROUP BY month. Works, but I'm loosing all interactive features of Qlikview. But I really do not understand how to do this nesting of aggrgations in QlikView itself. I have been reading the "NESTED AGGREGATIONS" chapter of the QlickView 8.5 handbook, but id didn't enlighten me. Any hints how to approach the "SUM(AVG(quantty) GROUP BY sku, month) GROUP BY month" issue in Qlikview itself and not in SQL?

--md

[gardan · Accepted solution]

It looks like you need to use the Aggr() function. The Aggr() function returns an array of values based on a given criteria. so if you want to sum up by month, the aggr function will return an array of sums by month that can be aggregated again. Here is a nice short video made by the Qlik team on the aggr() function and how to use it. Hope it helps!

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6758.Multi_2D00_pass-Aggregation.wmv:550:0]

[#3]

Thanks a lot, that solved it!

The basic code I needed was

sum(aggr(avg(quantity), sku))

I had problems with the embedded video, but downloading it from http://is.gd/908iE and playing it in VLS, worked.

Thanks!

--md

[#4]

both the links to the video doesn't work any more. I have got the same problem and want to know the solution too. Is the video still available somewhere?

[rbecher]

Open a new thread with your actual problem...

- Ralf