SET ANALYSIS WITH AGGR

[manu1512]

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

[Brett_Bleess]

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

[marcus_sommer]

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

[sunny_talwar · Accepted solution]

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))