Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set Analysis with AGGR excluding fields

Hello Everybody,

I am trying to set a Master Measure with set Analysis and the following function:

SUM(AGGR(SUM({$<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

It is a table with multiple MB values ans I only want the sum of Unique Hours per MB in 2019. The problem is that I don't want the selection to be affected by Dec1 and Dec2 fields, but this last condition doesn't work and the sum of hours is affected by them. Can you help me?

 

Thanks a lot

1 Solution

Accepted Solutions
Highlighted

Re: Set Analysis with AGGR excluding fields

Sometimes simple suggestion works like

SUM(AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Sometimes, Outer aggregation also needed. Not sure, How you are doing mathematically

SUM({$<[Year]={2019},Dec1= ,Dec2= >} AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Or

SUM({1<[Year]={2019},Dec1= ,Dec2= >} AGGR(NODISTINCT SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

2 Replies
Highlighted

Re: Set Analysis with AGGR excluding fields

Sometimes simple suggestion works like

SUM(AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Sometimes, Outer aggregation also needed. Not sure, How you are doing mathematically

SUM({$<[Year]={2019},Dec1= ,Dec2= >} AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Or

SUM({1<[Year]={2019},Dec1= ,Dec2= >} AGGR(NODISTINCT SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
Contributor
Contributor

Re: Set Analysis with AGGR excluding fields

Hi Anil, 

I tried to add the outer aggregation and it worked. 

SUM({$<[Year]={2019},Dec1= ,Dec2= >} AGGR(SUM({$<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Thank you very much for your quick response

Regards,