Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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?
@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)
@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,
With a pivot
Measures
if(SecondaryDimensionality()=0, null(), sum(Value))
if(SecondaryDimensionality()=0, avg(TOTAL <Material> Value))
if(SecondaryDimensionality()=0, StDev(TOTAL <Material> Value))
And with a table
Jan: sum({$ <Months={Jan}>} Value)
Feb: sum({$ <Months={Feb}>} Value)
...
avg(TOTAL <Material> Value)
StDev(TOTAL <Material> Value)
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
@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