topic Re: set analysis question in QlikView

set analysis question

gerrycastellino — Sat, 21 Apr 2018 03:55:25 GMT

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.

Re: set analysis question

sunny_talwar — Sat, 21 Apr 2018 11:55:54 GMT

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

Re: set analysis question

gerrycastellino — Mon, 23 Apr 2018 07:25:33 GMT

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.

Re: set analysis question

sunny_talwar — Mon, 23 Apr 2018 09:11:33 GMT

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

Re: set analysis question

gerrycastellino — Mon, 23 Apr 2018 16:40:26 GMT

Sunny:

Just says "No data to display".

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

Gerry.

Re: set analysis question

sunny_talwar — Mon, 23 Apr 2018 16:42:39 GMT

Will be difficult to say without having a look at it

Re: set analysis question

gerrycastellino — Mon, 23 Apr 2018 17:38:16 GMT

ok the vSelectionSpan is expanding to this ...

Re: set analysis question

sunny_talwar — Mon, 23 Apr 2018 17:52:16 GMT

the variable looks okay to me

Re: set analysis question

gerrycastellino — Mon, 23 Apr 2018 18:34:39 GMT

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.