New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
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)
• new to qlik view

1 Solution

Accepted Solutions
MVP

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.

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

3 Replies
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.
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

MVP

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.

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

Tags