Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

SET ANALYSIS WITH AGGR

Guys i have one query , here we are using aggr function to find out max sales by company and product , 

 

My query is why we are set analaysis twice , what will be result if we use only once in aggr function ?

 

query :Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))</year={$(=max(year)-1)}></year={$(=max(year)-1)}>

 

Kind Regards

MANU

Labels (1)
1 Solution

Accepted Solutions

Is your expression just this?

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

or do you have

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))</year={$(=max(year)-1)}></year={$(=max(year)-1)}> 

I don't really know what the part in red is for.

Going back to your question....

The reason the set analysis is used twice is because of the set analysis condition you have. You don't want the Sum of Sales for the current year, but the last year. Without selection in year field the below two expressions will work the same way

 

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

and

 

Max(aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

But when you select a single year (let's say 2019), the inner set analysis will be wanting to show 2018 and outer one without a set analysis will only want to show 2019 because 2018 is out of selection. Another expression which can work is this

 

Max({<year>} aggr(Sum({<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

View solution in original post

3 Replies
Highlighted
Digital Support
Digital Support

Manu, It will likely be more helpful if you can attach a sample app on this one as well, as I am sure the developers that could help likely need to see the underlying data model and expression in context of things in order to provide a proper answer for you on this one...

Here is a Help link that may be somewhat helpful though, and there are a couple of links at the bottom of the Help post that could provide further info as well that may help you get things sorted on your own here.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
MVP & Luminary
MVP & Luminary

It depends always on the context of an (nested) aggregation if you need to apply (the same or different one) conditions / set analysis and/or qualifier (total, distinct) on the inner and/or the outer aggregation. I think the best explanation about it could you find here: Set-Analysis-in-the-Aggr-function.

- Marcus

Is your expression just this?

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

or do you have

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))</year={$(=max(year)-1)}></year={$(=max(year)-1)}> 

I don't really know what the part in red is for.

Going back to your question....

The reason the set analysis is used twice is because of the set analysis condition you have. You don't want the Sum of Sales for the current year, but the last year. Without selection in year field the below two expressions will work the same way

 

Max({<year={$(=max(year)-1)}>} aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

and

 

Max(aggr (sum( {<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

But when you select a single year (let's say 2019), the inner set analysis will be wanting to show 2018 and outer one without a set analysis will only want to show 2019 because 2018 is out of selection. Another expression which can work is this

 

Max({<year>} aggr(Sum({<year={$(=max(year)-1)}>} Sale),Company_Name,Product))

 

View solution in original post