Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Variance calculation

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

1 Solution

Accepted Solutions
Not applicable
Author

I just done the calculation in Script.

PFA..,

Script:

Data:

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)

Resident Data

Group by GroupId;

Data2:

join(Data)

Resident Data;

Data3:

Resident Data

Group by GroupId;

Regards

Lathaa

3 Replies
Anonymous
Not applicable
Author

Hi,

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

KR

Elena

Not applicable
Author

Sorry Its not calculating the correct value...

PFA

Not applicable
Author

I just done the calculation in Script.

PFA..,

Script:

Data:

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)

Resident Data

Group by GroupId;

Data2:

join(Data)

Resident Data;

Data3: