Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Variance calculation

Hi all,

   I have a table like below :

    

GroupIdTransIdPoints
G11012
G11028
G11035
G11040
G11052
G21064
G21073
G31084
G31099
G311010
G411111
G51120
G51135
G51140

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Variance calculation

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

View solution in original post

3 Replies
elena888
Valued Contributor

Re: Variance calculation

Hi,

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

KR

Elena

Not applicable

Re: Variance calculation

Sorry Its not calculating the correct value...

PFA

Not applicable

Re: Variance calculation

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

View solution in original post