Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Josgac
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

Labels (4)
1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Josgac
Contributor
Contributor
Author

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,