Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Announcements
Qlik's 15-week Applied Data Analytics using Qlik Sense course. Classes start Jan. 17, 2022. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Not applicable
Author

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

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:

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

Community Browser