aggr() and set analysis

[tomdabek]

I am trying to build a formula that will show me the total volume for products that were sold at a lower price this year than last year.

It seems aggr can be used to solve this but I am having difficulty understanding the sequence of the function.

In my statement below, what i think should happen  is that:

the aggregation first considered is the outer one - the first one in the formula that aggregates using the dimensions shown at the very end of the formula - AreaID, [Plant Number] and Material_Num

So i guess qlikview arranges the data in a temp table with those dimensions and then examines the if statement.

The next part i am not clear- in evaluating the if statement, i have another aggr() - is this even necessary?  It is aggregating by the same dimensions.  However it is necessary to examine this line by line within the original aggr function - i want to look at each combination of my dimensions (AreaID, [Plant Number] and Material_Num) to see if in each case the price is less than last year.  If true then the if statement should be evaluated - sum({<year = {$(=max(year))}>}Volume)

Finally, the very first part of the entire formula is sum(aggr(...  and this is telling the formula to add up all these instances where the price is down from the previous year.

sum(aggr(

if(aggr(

sum({<year = {$(=max(year))}>}Revenue_Total)/sum({<year = {$(=max(year))}>}Volume),AreaID,[Plant Number],CustomerID,Material_Num) <

aggr(sum({<year = {$(=max(year)-1)}>}Revenue_Total)/sum({<year = {$(=max(year)-1)}>}Volume),AreaID,[Plant Number],CustomerID,Material_Num),

sum({<year = {$(=max(year))}>}Volume),0),AreaID,[Plant Number],CustomerID,Material_Num))

My questions are 1. Am I correctly understanding the logic and sequence of this?

2. The result is not correct (when i compare it to a more detailed listing that shows the dimensions of the aggr function above and each line is shown) - do i just have a formula error?

[swuehl · Accepted solution]

I think your basic understanding is correct.

If you have an advanced aggregation like

=OuterAggregation( Aggr( InnerAggregation, AggrDimensions))

then Qlik will create a virtual, temporary table similar to a straight table, with AggrDimensions as dimensions of that table and InnerAggregation as the single expression.

Finally, this table will be projected on the outer chart dimensions and aggregated using the OuterAggregation.

With regard to your expression, I don't think you need the additional advanced aggregations in your InnerAggregation, so this should be enough:

sum(  //OuterAggregation

    aggr(

          if( //InnerAggregation

              sum({<year = {$(=max(year))}>} Revenue_Total) / sum({<year = {$(=max(year))}>} Volume)

                          < sum({<year = {$(=max(year)-1)}>}Revenue_Total)/sum({<year = {$(=max(year)-1)}>}Volume),

              sum({<year = {$(=max(year))}>}Volume)

              ,0)

,AreaID,[Plant Number],CustomerID,Material_Num) //AggrDimensions

)

If this is still not showing the correct values, we might need to look into the details of your expressions and data model.

To debug this kind of expression, I often do the same as you described, I create a straight table with the AggrDimensions and the InnerAggregation, in this case maybe using additional expressions to show each part of the InnerAggregation.

You might also want to have a look into

Pitfalls of the Aggr function

and

Set Analysis in the Aggr function

[tomdabek]

Thank You

This was very helpful, a concise explanation to confirm how the nesting works.