standard deviation

[arun_kumar2]

Acct_No	Date	Usage	Std dev
2564865	Jan-18	135
2564865	Feb-18	83
2564865	Mar-18	80
2564865	Apr-18	75
2564865	May-18	120
2564865	Jun-18	86
2564865	Jul-18	135
2564865	Aug-18	100
2564865	Sep-18	78
2564865	Oct-18	86
2564865	Nov-18	35
2564865	Dec-18	150
2564865	Jan-19	122	31.16
2564865	Feb-19	86	30.02
2564865	Mar-19	120	29.93
946221	Jan-18	65
946221	Feb-18	45
946221	Mar-18	25
946221	Apr-18	42
946221	May-18	45
946221	Jun-18	78
946221	Jul-18	45
946221	Aug-18	66
946221	Sep-18	65
946221	Oct-18	54
946221	Nov-18	78
946221	Dec-18	63
946221	Jan-19	54	15.28
946221	Feb-19	15	15.04
946221	Mar-19	77	18.57

Hi all, i have to create the field called std dev, based off last 12 months of usage, (like a running average), restarting every acct no 

How can this be achieved? 

Any input is appreciated

thanks

[CarlDi]

Hi, you can try @AdamR_AYX's Moving Summary macro. I believe standard deviation is one of its functions.

http://www.chaosreignswithin.com/2014/12/moving-summarize.html

 

[arun_kumar2]

@CarlDi Thanks for your input. I went through the Summarize tool and indeed found a Standard deviation tool. 

However 2 things I have noticed:

1. The Std dev works for rows back and rows backward. Could this be adapted to date backward?

2. The Std dev is calculated as running total but it has to be restarted every account number?

The highlighted records should be null

I am fairly new to Alteryx, so please bear with me! :)

[CarlDi]

Ah gotcha. Try this workflow out - it addresses your screenshot. Basically, I used the regular summarize tool to generate the standard deviation then joined it back to the dataset. Hope this helps!

[arun_kumar2]

Hi CarlDi,

Thanks for the solution. It is close to the desired output. But 1 thing is that by using Last in the group by for 'DateTimeout', we are getting only 1 month of Std dev. 

Ideally, I would see Jan 19, Feb 19, Mar 19 std dev, since their previous 12 months are available.

What do you think? would a generate row tool be useful here?

Thanks

[CarlDi · Accepted solution]

@arun_kumar2 at that point, i split the Acct_nos to different streams plus added the moving summarize tool from the crewe macros once more. 

I understand that this isn't the most dynamic solution, especially if you have numerous account_nos, so I would go with a batch macro to streamline it.

https://help.alteryx.com/current/BatchMacro.htm

https://www.youtube.com/watch?v=YIAbQGQ_Hkg

Hope this helps!

[arun_kumar2]

Thanks for this solution!. Ill be sure to check it the Macros video! My Colleague has come up with a simpler approach. I have uploaded the wF for your perusal