Finding Medians using Set Analysis

Wed, 15 Jul 2015 13:30:05 GMT

I have a problem with making the Set Analysis ignore filters that are set for display when trying to calculate the Median won sales values to display as a column in a pivot table.

Original expression that was being affected by the selection of the Quarter_Label1 value, the vMinPrior4Qtr and v_maxCurrentQtr are date variables covering a 12 month period:

Num(Median(Aggr(Sum({$< WON_LOST={'Won'}, Quarter_Label1=, CLOSE_DATE={">=$(v_minPrior4Qtr)<$(=$(v_maxCurrentQtr)+1)"}>} SALESPRICE), OPPQTR)), '#,##0.0')

Each OPPQTR is a unique reference to a sales opportunity, to which multiple product line items can be associated with a SALESPRICE value.

I found from some research that the p() function should be used to overcome the fact that the changes are ignored in the aggregate.but I cannot make this work.

Revised attempt using the p() function in the Set Analysis, but this is still affected by the selection of Quarter_Label1:

Num(Median(Aggr(Sum({$<OPPQTR = p( {$<WON_LOST={'Won'}, Quarter_Label1=, CLOSE_DATE={">=$(v_minPrior4Qtr)<$(=$(v_maxCurrentQtr)+1)"}>} OPPQTR)>} SALESPRICE), OPPQTR)), '#,##0.0')

Elsewhere in my table, this expression shows the total won value and works fine:

SUM({<WON_LOST={'Won'}, Quarter_Label1=, CLOSE_DATE={">=$(v_minPrior4Qtr)<$(=$(v_maxCurrentQtr)+1)"}>} SALESPRICE))

Any suggestions?

Thank you

Sean

Re: Finding Medians using Set Analysis

Nicole-Smith — Wed, 15 Jul 2015 16:15:38 GMT

I think you may need to ignore the selection in both the SUM and the MEDIAN:

Num(Median({<Quarter_Label1=>}Aggr(Sum({$< WON_LOST={'Won'}, Quarter_Label1=, CLOSE_DATE={">=$(v_minPrior4Qtr)<$(=$(v_maxCurrentQtr)+1)"}>} SALESPRICE), OPPQTR)), '#,##0.0')

Let me know if that works out for you.

Re: Finding Medians using Set Analysis

Wed, 15 Jul 2015 16:26:15 GMT

This has resolved my problem.

Thank you

topic Re: Finding Medians using Set Analysis in QlikView

Finding Medians using Set Analysis

Re: Finding Medians using Set Analysis

Re: Finding Medians using Set Analysis