Calculation of the standard deviation

[beck_bakytbek]

Hi Folks,

i got a question:

how can you calculate the standard deviation, my table in excel does look like and the same i want to calculate in qlik:

Material	Jan	Feb	Mrch	Avg	Std
a	0	1	1	0,66666667	0,471404521
b	1	5	11	5,66666667	4,109609335
b	0	1	0	0,33333333	0,471404521

i tried to solve this issue, by using of this statement:

Stdev:

load

Material,

StDev(Month) as MyStdev

resident mytable group by Material;

But it does not work. The calculation in the table is from excel, I want to do the same calculation and results in Qlik Sense

Does anybody habe any idea how to resolve this issue?

Thanks a lot

[maxgro]

Try with RangeAvg function

T:
LOAD
*,
Sqrt(RangeSum(Pow((Jan - Average),2), Pow((Feb - Average),2), Pow((Mrch - Average),2)) / 3)
;
LOAD Material,
Jan,
Feb,
Mrch,
Avg,
Std,
RangeAvg(Jan, Feb, Mrch) as Average
FROM [lib://WebQlikCommunity]
(html, utf8, embedded labels, table is @1);

[MarcoWedel]

Can you please elaborate on your data model?

Your expression StDev(Month)  suggests there is a Month field containing the values you want to calculate the standard deviation from. Is this correct?

[beck_bakytbek]

@MarcoWedel  Hello Marco, yes it is my case: i want to calculate the standard deviation. my table does look like (see above table description). Do you have any idea?

Thanks a lot (Vielen Dank)

[beck_bakytbek]

@maxgro  Thank your very much for your feedback and help.

i got a question:

you have applied the code: the table is depivoted (3 dimensions: Jan. Feb, March).
How can I apply the code and formula if my data has not yet been pivoted, for example:

MyTable:

LOAD

Material,

Values

From MyTable;

Content of mytable:

Material, Months, Value

a, Jan ,0

a, Feb, 1

a, Mrch, 1

b, Jan ,1

b, Feb, 5

b, Mrch, 11

c, Jan ,0

c, Feb, 1

c, Mrch, 0

Do you have any idea how to resolve this issue?

Thanks a lot

Months,

[maxgro]

With a pivot

Measures

if(SecondaryDimensionality()=0, null(), sum(Value))

if(SecondaryDimensionality()=0, avg(TOTAL <Material> Value))

if(SecondaryDimensionality()=0, StDev(TOTAL <Material> Value))

[maxgro]

And with a table

Jan:      sum({$ <Months={Jan}>} Value)

Feb:      sum({$ <Months={Feb}>} Value)

...

avg(TOTAL <Material> Value)

StDev(TOTAL <Material> Value)

[maxgro]

Maybe there is another solution; you can add a new table Z2 in the script with the fields for the pivot

Z:
load * inline [
Material, Months, Value
a, Jan ,0
a, Feb,1
a, Mrch,1
b, Jan,1
b, Feb,5
b, Mrch,11
c, Jan,0
c, Feb,1
c, Mrch,0
];

// new table for the user interface

Z2:
load * inline [
NewColumn, Months, Type, Sort
Average, Jan, A, 30
Average, Feb, A, 30
Average, Mrch, A, 30
SDev, Jan, S, 50
SDev, Feb, S, 50
SDev, Mrch, S, 50
Jan, Jan, M, 10
Feb, Feb, M, 11
Mrch, Mrch, M, 12
];

The measure (NewColumn) is

IF(Type='M', Sum(Value),
IF(Type='A', Avg(Value),
IF(Type='S', StDev(Value)
)
)
)

and the NewColumn is "Sort by Expression" Sort

[beck_bakytbek]

@maxgro  Thank you very much for your time and help. 

I got question:

when i use this expression:StDev(TOTAL <Material> Value), i get completely different results regarding the standard deviation (please see excel). do i have to adjust this expression:StDev(TOTAL <Material> Value) in any way to get the values as in excel? Thank you for your help and your time