Question on limiting record set from expression

gerrycastellino — Thu, 19 Apr 2018 07:49:00 GMT

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.

Re: Question on limiting record set from expression

sunny_talwar — Thu, 19 Apr 2018 11:24:05 GMT

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

topic Question on limiting record set from expression in QlikView

Question on limiting record set from expression

Re: Question on limiting record set from expression