topic Variance calculation in Qlik Learning Discussions

Variance calculation

Fri, 13 Mar 2015 08:06:04 GMT

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

Re: Variance calculation

Anonymous — Sun, 15 Mar 2015 11:11:19 GMT

Hi,

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

Elena

Re: Variance calculation

Mon, 16 Mar 2015 03:28:57 GMT

Sorry Its not calculating the correct value...

PFA

Re: Variance calculation

Mon, 16 Mar 2015 08:26:26 GMT

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