Multi-Row Standard Deviation?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
