topic Re: Min, Median, Max agregated/sum by entity in QlikView

Min, Median, Max agregated/sum by entity

Mon, 29 Aug 2011 13:04:18 GMT

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.

swuehl — Tue, 06 Sep 2011 00:14:52 GMT

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

Tue, 06 Sep 2011 08:48:45 GMT

Stefan,

Many thanks for your help.

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.

swuehl — Tue, 06 Sep 2011 09:38:49 GMT

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