topic Tricky problem about std deviation calculation in QlikView

Tricky problem about std deviation calculation

Wed, 21 Nov 2012 18:06:15 GMT

Hi all,

Suppose I have one file with

City

Region

A (my first indicator)

B (my second indicator)

A city belongs in only one Region. So a Region is a set of cities.

We have build an application where an user select a city (just one), then we want to display :

(1) the value of A/B for the selected city

(2) the value of A/B for the region where the city belongs

(3) the value of the standard deviation (stdev) of A/B for the region where the city belongs

This calculation of standard deviation is needed to get a color coding of the 1)

if (1) < (2) - (3) => color in RED

if (2) - (3) < (1) < (2) + (3) => color in YELLOW

if (2) + (3) < (1) => color in GREEN

For (1) no problem

For (2) no problem using the P() function Sum( {< Region=P(Region) >} A ) / Sum( {< Region=P(Region) >} B )

For (3) I can't use of course Stdev & sum together,

Indeed we try with AGGR, but it seems AGGR doen't works with P()

Sorry to be long, maybe someone has the solution

Regards

CELAMBARASAN — Wed, 21 Nov 2012 18:18:31 GMT

May be you can use this concept

SD = Squareroot(Avg)

Can you tell me standard deviation should be computed on what basis?

Can you attach a sample.

Wed, 21 Nov 2012 18:39:54 GMT

standard deviation should be calculated like this

= sqr( [ (sum( a^2/b^2) - (sum(a)/sum(b))^2 ) ] / N ) huygens formula

where N is my number of cities in the region and ^2 is the power 2

Of course I prefer to use STDEV function instead of the previous formula.

Wed, 21 Nov 2012 19:49:36 GMT

This a sample

It seems to work with huyghens formula