topic Re: Standard Deviation in Dashboards & Analytics Discussions

Standard Deviation

KHK — Tue, 14 Mar 2023 14:56:37 GMT

Hi,

=IFERROR(STDEV.P(BQ14348:CB14348)/AVERAGE(BQ14348:CB14348),0)

Could please let me know how to convert above "STDEV.P" excel function in Incorta.

Re: Standard Deviation

JeffW — Tue, 14 Mar 2023 17:38:45 GMT

KHK,

Unfortunately, no we do not have a built in function for this. We have many of the functions that in aggregate support the calculation such as ABS(), AVERAGE() and SQRT(). In a multi pass process it would look something like:

for each data point x calculate distance from mean, squared: abs((x-avg(x))(x-avg(x)))
sum each of the above, and divide by count(x)
sqrt(#2)

However, the easier way to do this is by using an python MV and using a python dataframe function to calculate the STDEV from the elements in the dataframe. Pandas supports something like

df['amount'].std()

Re: Standard Deviation

JeffW — Tue, 14 Mar 2023 18:05:04 GMT

Re: Standard Deviation

ZachBreimayer — Thu, 16 Mar 2023 16:25:57 GMT

Adding on to Jeffs approach, if you happen to be more SQL oriented you can alternatively use SparkSQL or Postgres SQL 'STDDEV' Function within a Materialized View.

Output:

Similarly, if we want to show the standard deviation grouped by product in the same scenario here is a simple example.

With Group By:

Output:

Regards,

Zach

Re: Standard Deviation

nmaiya — Tue, 29 Aug 2023 06:58:08 GMT

Hello JeffW,

I'm working with a dataset where the grouping criteria are determined by specific requirements and are not fixed or limited.

Typically, other aggregate functions operate on the columns designated in the grouping context. Is it possible to apply the stddev() function in a manner similar to other aggregate functions such as sum and average, considering this dynamic grouping?

Re: Standard Deviation

mhornak — Mon, 30 Oct 2023 08:19:10 GMT

Hello All,

thank you for your advice. I would ask one question regarding that.

I was able to calculate abs(x - average(x))*abs(x - average(x)), but in next colum I would get sum of these row and there are not correct numbers.

I was using formula below like Sum with Group by, but it does not calculate correctly.

sum(

(abs(

sum(

ds_AmaGIzL8G.insight.quantity,

groupBy(

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2,

ds_AmaGIzL8G.insight.Weeknum_Cube

)

) - sum(

ds_AmaGIzL8G.insight.quantity,

groupBy(

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2

)

) / 6

) * abs(

sum(

ds_AmaGIzL8G.insight.quantity,

groupBy(

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2,

ds_AmaGIzL8G.insight.Weeknum_Cube

)

) - sum(

ds_AmaGIzL8G.insight.quantity,

groupBy(

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2

)

) / 6

)),

groupBy(

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2

)

Please could you help me, what could be wrong?

Many thanks.

Re: Standard Deviation

JoeM — Fri, 03 Nov 2023 13:49:02 GMT

@mhornak -

Are you grouping by the following in your aggregated table insight? Also, to be safe, change the aggregation method to formula.

ds_AmaGIzL8G.insight.code,

ds_AmaGIzL8G.insight.branch_code,

ds_AmaGIzL8G.insight.DIV2

Re: Standard Deviation

mhornak — Thu, 09 Nov 2023 12:20:18 GMT

Many hanks, I was not aware that grouping in insight has impact on this calculation.