Dollar expansion of Median() in set expression

[ToniKautto]

I am trying to calculated the sum of cost per item, only including costs that are equal or greater than the median cost per item.

The obvious problem is that the dollar-expansion is calculated over the total data, so I attempted to make a simple Aggr() to group the result over Item.

SUM({$<Cost={">=$(=aggr(median(Cost), Item)"}>}Cost)

After numerous attempts of twisting and turning the expression around I have still failed to find a way to do this in a set expression, so I am now hoping that a set analysis genious in here could point me in the right direction. It seems like something quite easy to accomplish, so what am I missing out on here?

In the attached QVW you can see that the result can be found by an Aggr(If(Median())) construction in the third column. So these are the expected results once the set expression expands and populates the second column. The Data table shows the loaded data.

[#2]

Is there any reason you can't just calculate the median values in your load script? Attached is an example.

[ToniKautto]

Yes, the main reason being that I want to know how to get the set expression to work.

Thanks any way for the suggestion.

[Oleg_Troyansky]

Tony,

there are a few issues here:

1. AGGR() returns an array of values, in your case, associated with Item Numbers. Generally speaking, it's not always correct to compare a single number to a result of AGGR. In this specific case, the fact that Item is also a dimension in the chart, renders the unique result, but it can't always be used like this.

2. The main issue is that your Set Analysis condition is dependant on Item, which is a dimension. This can't work because Set Analysis conditions are evaluated once per chart, not once per cell. So, Set Analysis condition cannot be sensitive to chart dimensions. That's why your condition is not working.

If your data set allows, use IF (I can't believe I actually recommend IF to anyone...) If your data is huge, follow Leonard's advice and pre-calculate the flag, based on a condition that the cost is above the median.

cheers,

Oleg Troyansky

[ToniKautto]

Guess I will have to forfit my idea of using only set analysis for this scenario.

Thanks for your time guys, appreciate the thoughts!