Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

martin_caruso
New Contributor II

Function STDEV.P() in KPI Chart

Hello good afternoon!


I was needing to replicate the Excel STDEV.P () function in a KPI Chart.


I have this pivoting table. I need to calculate in a KPI Chart the function STDEV.P () on the Totals line.


Pivot table.PNG

The Set Analysis that I am using is the following:


Stdev(

aggr(

    sum({<[Status DESC] = {'Ended'}>} [Qty cert])

    , PERIOD, DESC, [Number ID], [N° Line])

)

The value that the function gives me in Qlik is 25.40 and in Excel it is 78.42. The correct result is the one that you throw in Excel. Could anyone tell me what I'm doing wrong?


I enclose the Excel so you can see the expressions for anything. I already appreciate any kind of help you can give me.


Regards,



Martin.

1 Solution

Accepted Solutions

Re: Function STDEV.P() in KPI Chart

I guess it is period... try this

=Stdev(Aggr(Sum({<[Status DESC] = {'Ended'}>} [Qty cert]), PERIOD))

*

sqrt((Count(PERIOD) - 1)/Count(PERIOD))

5 Replies

Re: Function STDEV.P() in KPI Chart

Not sure what you have, but this worked for me

=Stdev(F1) * sqrt((Count(F1) - 1)/Count(F1))

Where I created F1 as an Inline table field like this

LOAD * INLINE [

    F1

    42.90

    10.00

    12.00

    10.40

    12.00

    293.60

    8.40

    11.00

    7.00

    6.00

    11.00

    2.00

];

Also, look here

Standard Deviation (Population vs. Sample)

martin_caruso
New Contributor II

Re: Function STDEV.P() in KPI Chart

Thank you very much Sunny, but it does not work for me. The problem is that F1 is not a dimension, it is an expression in my model.

In my pivot table, the value of q_cert is determined as follows:

Sum ({<[Status DESC] = {'Ended'}>} [Qty cert])

This expression would be equal to Sum(F1) in your model.

Re: Function STDEV.P() in KPI Chart

What is the dimension over which you are looking to calculate the Stdev of the above expression?

Re: Function STDEV.P() in KPI Chart

I guess it is period... try this

=Stdev(Aggr(Sum({<[Status DESC] = {'Ended'}>} [Qty cert]), PERIOD))

*

sqrt((Count(PERIOD) - 1)/Count(PERIOD))

martin_caruso
New Contributor II

Re: Function STDEV.P() in KPI Chart

Excellent Sunny! There it worked! Thank you very much!

Community Browser