Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ) )
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 ) )
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
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 ) )
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
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 ) )
Genius
Thanks so much!