topic Re: SET ANALYSIS WITH AGGR in QlikView

SET ANALYSIS WITH AGGR

manu1512 — Sat, 16 Nov 2024 02:54:25 GMT

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

Re: SET ANALYSIS WITH AGGR

Brett_Bleess — Tue, 20 Aug 2019 12:22:30 GMT

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/define-aggregation-scope.htm

Regards,
Brett

Re: SET ANALYSIS WITH AGGR

marcus_sommer — Tue, 20 Aug 2019 15:13:36 GMT

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

Re: SET ANALYSIS WITH AGGR

sunny_talwar — Wed, 21 Aug 2019 11:00:15 GMT

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