Hi Kristy,
your file didn't successfully attach, I'm afraid. What I'd like to do, though, is explain aggr() and give you a method for how to convert your requirement into the appropriate expressions. This will hopefully help you, then, with other problems. It does mean I'm going to answer in some length, though, so stay with me!
The aggr() function lets you calculate new fields for your data model on-the-fly. This is especially useful when you are designing a chart, and the dimension you want for your chart isn’t one of the fields already in your data model. In fact, you can think of aggr() as creating a new table of data in your data model, containing the already-existing fields plus one new field that is calculated for each combination of the already-existing fields.
Let’s illustrate with an example. Suppose I am a chair salesman (very stylish, Scandinavian chairs, of course). I have gathered sales data in a table that has 3 fields: the Customer, the SaleDate, and the NumberOfChairs sold to the customer on that date. I want to make a bar chart showing how many customers have bought 1-5 chairs, 5-20, or 21+. How to do this? Clearly, the dimension on my chart is a sales range, but my data doesn’t explicitly contain the range that my customers fall into.
You can see, can’t you, that this challenge would probably be easier if my original data had a summary table that showed Customer and total number of chairs sold. In fact, it really would be easy if the summary table simply had the sales range for each customer (where sales range is one of the values “1-5”, “5-20” or “21+”). This where aggr() comes in. If you can complete this sentence: “this problem would be easy if my data model had the X for each combination of values of A, B, C, …”; then you simply want aggr(X, A, B, C, …) as your dimension expression. QlikView will then calculate X for each A, B, C, …; QlikView will dynamically associate it with the rest of your data; and you can then make your chart expression to be whatever it needs to be for each value of X.
Well, then, apparently I therefore want (in pseudocode) aggr(sales range, Customer). But how do we calculate sales range? Well, after a touch more thought, we realise this is actually (still in pseudocode) aggr(IF total number of chairs sold<=5 THEN ‘1-5’ ELSE IF total number of chairs sold<=20 THEN ‘6-20’ ELSE ‘21+’, Customer). Calculating the total number of chairs sold is easy, and this gives us our final expression:
aggr(
IF( SUM(NumberOfChairs)<=5 , ‘1-5’,
IF( SUM(NumberOfChairs)<=20, ‘6-20’,
‘21+’
)),
Customer
)
Ta Da! We add this to our bar chart as a calculated dimension. Then, because we want the bars themselves to be the number of customers, we specify our chart expression to be COUNT(Customer).
Let’s now take a step back and reverse-engineer what QlikView is doing here when it generates our bar chart.
- First, it examines the Dimensions for calculated expressions and finds our aggr()-based expression.
- QlikView then proceeds to evaluate our IF… expression for every value of Customer. In other words, it goes to our original data table, and then for each Customer it sums NumberOfChairs and uses our logic to decide if this Customer’s respective aggregated value is ‘1-5’, ‘6-20’ or ‘21+’. This results in a new table with Customer as one column and our calculated sales range for each customer as the other.
- QlikView then automatically links this table into our existing data model i.e. to our original table with its columns of Customer, SalesDate and NumberOfChairs. Hence, the data model now has two tables: the original, and the new one generated by the aggr().
- QlikView now uses the first parameter of the aggr() – which is our calculated sales range field - as the dimension for the chart.
- QlikView next proceeds to evaluate the chart’s expression, COUNT(Customer). This is actually very simple for QlikView in this example, because the table resulting from aggr() has Customer as one of its columns anyway!
So, now QlikView has calculated everything it needs to generate the chart we desire.
So, remember the key to solving the problem: complete the sentence “this problem would be easy if my data model had the X for each A, B, C, …”.
Let me know how you go!
Angus.
