Median with Sum Expression

[#1]

So I have some values that I need to sum for each day and then extract median from all those sums.

For example:

10 January  = sum(1 4 5 8 7 4 6) = 35

15 January = sum(5 4 8 9 1 ) = 27

16 January = sum( 5 4 8 1 2 ) = 20

median(all those sums) = 27

I tried doing this:

Median (sum(IM)) but it did not return any results

Any advice? I can create a table with all the dates and sums, maybe there is a way to use that column from the table for median? Really stuck here, thanks

I also tried this:

median(aggr(sum(IM), [Exchange])))

But it sums all the IM and I need to introduce Date field into this somehow to make sure it sums all IM for each date and then gets median from those sums

[swuehl]

median(aggr(sum(IM), [Exchange])))

This approach should lead to the correct results, if you use the correct dimensions in your advanced aggregation.

If you need to aggregate per Date, use Date as dimension:

=median( aggr( sum(IM), Date) )

Not sure why you introduced Exchange field here, so you might need to to explain your data model a bit closer.

In general , you can use multiple dimensions to aggr() function, like in a table chart:

=median(aggr(sum(IM), [Exchange], Date ) )

[#3]

brilliant, this seemed to work, thanks!

i have date field as i need to sum all IM per date

and then exchange field, as i need to produce a chart to show IM for each exchange

could you help me a bit more please as i need to introduce set analysis into this. I need to make sure that value I for IND is always selected and data doesn't change if the user selects smth else, in other words, this selection should be fixed

I tried this:

=median(aggr(sum( {1<[IND] ={'I'}>} IM), [Exchange], Date ) )

but then the results get messed up, did i put it in the right place and did i use the correct syntax?

thanks

[swuehl]

It's just guessing around without knowing your setting, but in general, median() is an aggregation function like sum() or avg(), so you can apply set analysis also to median() [and you might need to]:

=median( {1<IND = {'I'}>} aggr(sum( {1<[IND] ={'I'}>} IM), [Exchange], Date ) )

[#5]

if i do this then the field gets fixed but so does the exchange so it shows median for all the exchanges and it they should change depending on user selection

for example

if the user selects company A, it should show medians for each exchange applicable to that company only

if the user selects a different company, the medians and exchanges should change accordingly

at the moment it shows all the possible exchanges and medians overall for all the firms and if a different firm gets chosen, it just stays the same

i also tried this

=median( {1<IND = {'I'}>} aggr(sum( IM), [Exchange], Date ) )

but the chart just stays the same and reacts if IND field is changed

[swuehl · Accepted solution]

That's because you are using set identifier 1 in your set analysis, try the default set:

=median( {$<IND = {'I'}>} aggr(sum( {$<[IND] ={'I'}>} IM), [Exchange], Date ) )

[#7]

Genius

Thanks so much!