Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

abyqlik_com
Contributor

AGGR...

The AGGR Function.....

Capture.JPG

I thought I would start this blog post with a simple multiple choice question:

Is AGGR.....

a - Used in many QlikView applications to great effect
b - Mis-used in many QlikView applications
c - Used to return an answer without really knowing why you get the answer
d - Not used at all because we're "not quite sure what is does"
e - All of the above

The answer in my opinion is "e - All of the above".

When I came to write this post and indeed the technical brief, the hardest part of all was actually coming up with a really good, easy to understand description of AGGR, as it has to make sense to both "technical" and "not so technical" people. I finally settled on the one below...

When it is used, the AGGR function produces a virtual table, with one expression and grouped by one or more dimensions. The contents / result of this virtual table can then be used / aggregated by a further outer aggregation function(s).

With this definition in mind, I have produced a Technical Brief and application which can be found here and here. In this document I have tried to provide an overview of the function and provided some examples of where it can be used to great effect.

So, when should you use AGGR? The answer is: Whenever you want to perform an aggregation in two steps. In the technical brief we show you some scenarios like.....

  • Largest average order value for each Salesperson for each Country, where average order value for each Salesperson is the first calculation and the largest in each Country is the second.
  • The Salesperson responsible for the largest average order value in each Country
  • How many Salespeople have average order values of less than $100 in each Country

I would also be interested in any other creative and powerful uses of AGGR you may have.


Thanks.

ABY

19 Comments
kullenberg
New Contributor

Hej

sounds interesting, I cant access the link however (not authorized).

thanks

Magnus

0 Likes
178 Views
MVP
MVP

Hi Magnus,

The document should be available now. Please check and get back to us if that does not work.

Kind regards.

Miguel

0 Likes
178 Views
Gabriel
Valued Contributor II

Hi Adam,

Brilliant piece of blog...... great work

Regards,

Gabriel

0 Likes
178 Views
rva_heldendaten
Contributor III

Great work!

Still two questions on AGGR that are not answered.

- What's the difference between "AGGR(DISTINCT" and "AGGR(NODISTINCT".  Has anyone a good example for this?

- According to the documentation one can place SET ANALYSIS in 3 different places in an AGGR expression. For example to create a SET for Year=2013:

avg({$<Year={2013}>}AGGR(DISTINCT {$<Year={2013}>} sum({$<Year={2013}>}  Sales),Country))

Are all  3 Sets necessary to retrieve the correct result?

178 Views

A trick somebody posted in a discussion on this site: use it in an expression for a listbox to hide the excluded items: aggr(FieldName,FieldName)

178 Views
Not applicable

An useful and simple place to use the AGGR function as a dimension is in a Multi Box, you're creating a "virtual and dynamic" new field that you can use to make selections.

0 Likes
178 Views
cheburashka
Contributor III

Hello,

I'm still not able to see the docx document.

The response after clicking the link is the following:

" It appears you're not allowed to view what you requested. You might contact your administrator if you think this is a mistake. "

, Thx for your help

0 Likes
178 Views
MVP
MVP

Yes, it's fixed now. Get back to us in case you cannot download it.

Kind regards.

Miguel

178 Views
cheburashka
Contributor III

Solved, thx

0 Likes
178 Views
pauljohansson
Contributor II

Hi Roland,

Lets say you have the dimensions Continent, Country, Office &  measure Sales in a straight table.

Using expression aggr (sum(Sales),Continent) gives you a table showing sales per continent, one row per Continent.

Using aggr (NODISTINCT sum(Sales),Continent) still gives you the sales per continent, however, it will repatedly show the values depending on how may Country & Offices there are.

br

Paul

0 Likes
178 Views
cje
Contributor

Hi,

Still can't access to the document.

Forbidden.

Thxs

0 Likes
178 Views
mphekin12
Valued Contributor

Still getting the following error:

Unauthorized

It appears you're not allowed to view what you requested.  You might contact your administrator if you think this is a mistake.

Thanks

0 Likes
178 Views
jcarpenter9
Contributor

Adam, thank you for the comprehensive explanation of aggr(). I wish this documentation had been part of the online help when aggr() was introduced, because then it wouldn't have taken me so long to figure out how it works and when to use it. This document should clear away some of the "aggravation".

0 Likes
178 Views
jolivares
Valued Contributor

Saludos Miguel Angel... pudieras ver este anexo y ver como puedo resolverlo.  He tratado usando la funcion Aggr, pero no he podido dar con la solucion.

gracias.

No puedo agregar el ejemplo... como se lo hago llegar?

0 Likes
178 Views
Not applicable

Well speaking of typical use its definitelly avg stock value for each item in every warehouse.

Thanks for tutorial btw

0 Likes
178 Views
jdiaz
New Contributor III

Greetings!

Thanks for posting the article. I am not sure of whether or not I should ask this question here, so I'll apologize in advance and ask for assistance with the following issue that I am facing when using AGGR in what I expected to be a very simple expression - which is not showing the results I think I should get.

In the pivot table on the left side of the image bellow, you will find generated sales data for sales people over a four month period.  If you look at data for one of the salesmen, say "T", and mentally calculate the average number of sales for the four month period, it would come to be 5.5.  However, if I use AGGR to do this calculation as a second expression in the chart and table to the right (using only Salesman as dimension) the value will not be correct (QV calculates it to be 54.3636).  Furthermore, if one selects the value of "T" for Salesman, the avg is calculated to be 5.9.  If I remove the nodistinct parameter from aggr, only three of the sales people will show results (O, P and T), but still all incorrect. However, selecting any of the three sales people which appear to have (incorrect) results will further show its correct value for the average number of sales transactions per month.

Problem with inner AGGR.JPG.jpg

Can someone explain why is this happening?

Thanks!

--José

0 Likes
178 Views
jolivares
Valued Contributor

Envíame el qvw para verlo

Juan Olivares

0 Likes
178 Views
Not applicable

I have read and used the AGGR technical brief many times - a great document.

It is amazing how many QV developers dont know or really understand this function - and hence problems try get solved in the data model etc.

By far the best function in QV!

0 Likes
178 Views
borisman
Contributor III

I would love to hear some comments too on your second question!

0 Likes
178 Views