Multi-Row Standard Deviation?

Question

hello, i am writing a moving average using the multi-row tool like this:

and i would like to also calculate a rolling standard deviation to match this moving average.. i imagined it looking something like this:

But it looks like alteryx doesn't have a standard deviation function.... Can someone please help me figure out how to implement this?

OllieClarke · Accepted Answer

Hey @Matthew

I wouldn't use the multi-row for this, but rather generate rows, join and summarise as here:

You're basically using the generate rows to create a lookup matrix which you can use to join the relevant rows onto themselves. Then you can make use of all the aggregate functions in the summarise tool to do your moving calcs.

Hope that helps,

Ollie

Matthew · Accepted Answer

thanks for your suggestion @OllieClarke, but your solution would have been too computationally expensive. i'm working with over 80 million rows, so using the Generate Rows tool would have made the dataset balloon too much (i probably should have mentioned that)

but i think i got a solution working:

I calculated the StdDev using the Multi-Row and Formula tool

i started by getting the moving average

then calculated the squared difference

then got the sum of the squared difference

then got the variance and StdDev from that

i feel like alteryx should have a StdDev function, but i found a way around :)