Finding Medians using Set Analysis

[#1]

Hi

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

[Nicole-Smith · Accepted solution]

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.

[#3]

This has resolved my problem.

Thank you