Variance calculation

[#1]

Hi all,

   I have a table like below :

GroupId	TransId	Points
G1	101	2
G1	102	8
G1	103	5
G1	104	0
G1	105	2
G2	106	4
G2	107	3
G3	108	4
G3	109	9
G3	110	10
G4	111	11
G5	112	0
G5	113	5
G5	114	0

Each groupId has one or more transactionId tat in turn corresponds to some value . ie,Points

Now I want to calculate the variance - Using the formula

summation of ( X- mean(X)) ^ 2 / N

For example for G1 we have 5 transaction so N=5  mean(points) = 17/5 =3.4

Now the formula should be G1 =( (2-3.4)^2 + (8-3.4)^2 + (5-3.4)^2 +(0-3.4)^2 +(2-3.4)^2 ) /5

Can someone help me out - how to achieve this kind of expression in Qlikview?

do we have any variance function in QV!!

If so, tell me how to use it for this kind of scenario?

Regards

Lathaa

[Anonymous]

Hi,

you can use GroupId as dimension and pow(Stdev(Points),2) as expression.

KR

Elena

[#3]

Sorry Its not calculating the correct value...

PFA

[#4 · Accepted solution]

I just done the calculation in Script.

PFA..,

Script:

Data:

LOAD * INLINE [

    GroupId, TransNo, Points

    G1, 101, 2

    G1, 102, 8

    G1, 103, 5

    G1, 104, 0

    G1, 105, 2

    G2, 106, 4

    G2, 107, 3

    G3, 108, 4

    G3, 109, 9

    G3, 110, 10

    G4, 111, 11

    G5, 112, 0

    G5, 113, 5

    G5, 114, 0

];

Data1:

join(Data)

Load GroupId,Avg(Points) as Avg

Resident Data

Group by GroupId;

Data2:

join(Data)

Load GroupId,TransNo,Points,Avg,(Points-Avg)as Points1,Pow((Points-Avg),2) as Value

Resident Data;

Data3:

Load GroupId,Sum(Value),Sum(Value)/Count(GroupId) as Value1

Resident Data

Group by GroupId;

Regards

Lathaa