topic Re: Median with Sum Expression in QlikView

Median with Sum Expression

Fri, 05 Apr 2013 08:25:34 GMT

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

Re: Median with Sum Expression

swuehl — Fri, 05 Apr 2013 08:30:54 GMT

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 ) )

Re: Median with Sum Expression

Fri, 05 Apr 2013 08:41:04 GMT

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

Re: Median with Sum Expression

swuehl — Fri, 05 Apr 2013 08:55:19 GMT

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 ) )

Re: Median with Sum Expression

Fri, 05 Apr 2013 09:06:37 GMT

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

Re: Median with Sum Expression

swuehl — Fri, 05 Apr 2013 09:09:26 GMT

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 ) )

Re: Median with Sum Expression

Fri, 05 Apr 2013 09:12:53 GMT

Genius

Thanks so much!