Multi-Row Standard Deviation?

[Matthew]

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 solution]

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 solution]

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 :)

[OllieClarke]

@Matthew Nice

With stats functions, there's always the option of doing it by hand 😂. I had a test, and breaking it down your way was 10x faster than the generate rows and join approach. I generally avoid writing multi-row formulas like yours, because they're very static (and annoying to write), but absolutely worth it in this case

[Matthew]

@OllieClarke  I feel the same way about the multi-row tool. It requires so much prep and cleaning to make it function properly, and modifying them is annoying. But this will work until there is a built in stdDev function

Thanks for doing the performance comparison!