Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- aggr() and set analysis

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

tomdabek

Contributor III

2016-05-24
03:35 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

aggr() and set analysis

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?

- Tags:
- aggr set analysis

1,557 Views

1 Solution

Accepted Solutions

swuehl

MVP

2016-05-24
06:58 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

and

2 Replies

swuehl

MVP

2016-05-24
06:58 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

and

tomdabek

Contributor III

2016-05-30
08:09 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank You

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

576 Views