set analysis question

[gerrycastellino]

I need to create a expression which limits the worker type =  that is in an island column called %Spans_Worker_Type

Where do I insert the set analysis,  the choices from the default state are bleeding into this expression for worker type:

rangesum(sum({<[Worker  Type] = P(%Spans_Worker_Type)>}AGGR({<[Worker  Type] = P(%Spans_Worker_Type)>}if(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}, [Worker  Type] = P(%Spans_Worker_Type)>}TOTAL_ROLL_UP_COUNT)<=2,1),SL_SPRVSRY_ORG_WID)))

My dimension is :

=aggr(only({<[Worker  Type] = P(%Spans_Worker_Type)>} [Worker  Type]), [Worker  Type])

Trying to count the #managers , where direct reports <=2

Gerry.

[sunny_talwar]

This should work I think

Dimension

Worker Type

Expression

Sum({<[Worker  Type] = P(%Spans_Worker_Type)>}Aggr(If(Sum({<%MAIN_ORG_SpanSumm = {$(vL2MainOrg)}, [Worker  Type] = P(%Spans_Worker_Type)>} TOTAL_ROLL_UP_COUNT) <= 2, Avg({<[Worker  Type] = P(%Spans_Worker_Type)>}1)), SL_SPRVSRY_ORG_WID))

[gerrycastellino · Accepted solution]

wow ! that was perfect.

Thank you so much !

I have a similar expression that is sort of similar syntax, thought it would just convert as you had up above,  but not quite getting it.

Original:

rangesum(sum({<[Worker  Type] = {'Regular Employee'}>}AGGR(if(Count({<$(vSelectionSpan), [Worker  Type] = {'Regular Employee'}>} EMPLOYEE_ID_Span_Layers)<=2,1),SL_SPRVSRY_ORG_WID)))

Converted:

sum({<[Worker  Type] = {'Regular Employee'}>}AGGR(if(Count({<$(vSelectionSpan), [Worker  Type] = {'Regular Employee'}>} EMPLOYEE_ID_Span_Layers)>=1 and

Count({<$(vSelectionSpan), [Worker  Type] = {'Regular Employee'}>} EMPLOYEE_ID_Span_Layers)<=2,Avg({<[Worker  Type] = {'Regular Employee'}>}1)),SL_SPRVSRY_ORG_WID))

I'm just doing a range now, instead of <=2.

Gerry.

[sunny_talwar]

Looks okay to me syntax wise... is this giving you an error or incorrect output?

[gerrycastellino]

Sunny:

Just says "No data to display".

I know for sure there are records,  something is getting hung up in this expression.

Gerry.

[sunny_talwar]

Will be difficult to say without having a look at it

[gerrycastellino]

ok the vSelectionSpan is expanding to this ...

[sunny_talwar]

the variable looks okay to me

[gerrycastellino]

The variable is interfering with the calculation, if I take it out of the calculation, it executes,  but gives me a wrong result, but it executes,  something in the variable syntax is causing the entire expression to blow up.