Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
gerrycastellino
Creator III
Creator III

Question on limiting record set from expression

I have an expression as follows:

pick(match($(Choice)),0,1),

AGGR(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>}TOTAL_ROLL_UP_COUNT),MANAGER),

AGGR(Count({<$(vSelectionSpan)>} Distinct EMPLOYEE_ID_Span_Layers),MANAGER))

I need to limit the records where the TOTAL_ROLL_UP_COUNT per MANAGER is <=2  (for first line)

or the distinct EMPLOYEE_ID_Span_Layers <=2  per manager (for second line)

So I came up with this, this gives me the right result set, but I need to come up with just 1 NUMBER  - which is the MANAGER count.

My dimensions are Year/Month.

pick(match($(Choice),0,1),

AGGR(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>}TOTAL_ROLL_UP_COUNT)<=2,MANAGER),

AGGR(Count({<$(vSelectionSpan)>} Distinct EMPLOYEE_ID_Span_Layers)<=2,MANAGER))

Gerry.

1 Solution

Accepted Solutions
sunny_talwar

May be this

Pick(Match($(Choice), 0, 1),

Aggr(If(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>} TOTAL_ROLL_UP_COUNT) <= 2, Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>} TOTAL_ROLL_UP_COUNT)), MANAGER),

Aggr(If(Count({<$(vSelectionSpan)>} DISTINCT EMPLOYEE_ID_Span_Layers) <= 2, Count({<$(vSelectionSpan)>} DISTINCT EMPLOYEE_ID_Span_Layers)), MANAGER))

View solution in original post

1 Reply
sunny_talwar

May be this

Pick(Match($(Choice), 0, 1),

Aggr(If(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>} TOTAL_ROLL_UP_COUNT) <= 2, Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}>} TOTAL_ROLL_UP_COUNT)), MANAGER),

Aggr(If(Count({<$(vSelectionSpan)>} DISTINCT EMPLOYEE_ID_Span_Layers) <= 2, Count({<$(vSelectionSpan)>} DISTINCT EMPLOYEE_ID_Span_Layers)), MANAGER))