community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

standard deviation

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

Alteryx
Alteryx

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

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

 

Highlighted

@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?

Capture.PNG

 

The highlighted records should be null

 

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

Alteryx
Alteryx

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!

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

Alteryx
Alteryx

@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. 

 

Std_dev.jpg

 

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!

A batch macro is a special kind of macro that is typically needed to process a group of records based on a control parameter. The control parameter determines which group of records will be processed through the underlying macro logic. The macro will be run from beginning to end for each control ...

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

Labels