3 Replies Latest reply: Sep 6, 2011 5:38 AM by Stefan Wühl

Min, Median, Max agregated/sum by entity

Hi,

I have a table like the one below.

ENTITY,   YEAR,   MONTH,   VALUE

ABC,   2011,   1,   11
ABC,   2011,   2,   10
ABC,   2011,   3,   9

QWE,   2011,   1,   19
QWE,   2011,   2,   20
QWE,   2011,   3,   21

XYZ,   2011,   1,   14
XYZ,   2011,   2,   15
XYZ,   2011,   3,   16

I want do creat a gauge chart, where appears the Min, Median and Max of the "Value" field, agregated/sum by "Entity".

Min          Median          Max

ABC          XYZ             QWE

30               45               60

|                  |                  |

#######################

Is this possible?

Many thanks,

Pedro.

• Re: Min, Median, Max agregated/sum by entity

I think you could use soemthing like:

=min({<ENTITY = {'ABC'}>} VALUE)

=Median({<ENTITY = {'XYZ'}>} VALUE)

=max({<ENTITY = {'QWE'}>} VALUE)

to set the lower limits of the segments in the gauge chart.

Please have a look to the attached sample.

Regards,

Stefan

• Re: Min, Median, Max agregated/sum by entity

Stefan,

However I don't want to specify the entity in each formula.

It may vary after adding more data...

The result I need in these case is 30 (for the minimum), 45 (for the median) and 60 (for the maximum).

Regards,

Pedro.

• Re: Min, Median, Max agregated/sum by entity

Hi Pedro,

I am beginning to understand.

So you could maybe use following expressions for your segment lower borders:

=min(aggr(sum(VALUE), ENTITY))

=median(aggr(sum(VALUE), ENTITY))

=max(aggr(sum(VALUE), ENTITY))

Regards,

Stefan