# 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
Did you mean:
Highlighted 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

Labels (4)

• ### Set Analysis

1 Solution

Accepted Solutions
Highlighted MVP

## 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)
2 Replies
Highlighted MVP

## 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)
Highlighted 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,