Calculating Growth Rates to Create Charts

[thenning]

Hi!  I'm a new user to Alteryx and I'm looking to get some guidance on how I might be able to complete the following tasks using the data I've provided in the attached workflow.  

In selecting a specific month (let's use January 2019 as an example), I want the following information to be generated:

• Annual growth rate
  • Compare January 2017 to January 2018 and January 2019 (percentage change)
• Quarterly growth rates
  • Need to calculate the percentage change for all quarters compared to the prior quarter (for example, FY19Q1 vs. FY18Q4, FY18Q4 vs. FY18Q3)
  • Need to calculate the percentage change for all quarters compared to the same quarter the prior year (for example, FY19Q1 vs. FY18Q1)
• Rolling 12-months
  • Need to calculate a rolling 12-month average (for example, for January 2019, it would be February 2018-January 2019 and for February 2019, it would be March 2018-February 2019)
  • Calculate the growth change from month to month.

Ultimately, I'm trying to get raw tables that I can create charts off of (if that helps).  Please let me know if you have any additional questions or have any ideas.  Any help is appreciated.

Thanks!

[mceleavey · Accepted solution]

Hi @thenning ,

I've attached a workflow which should do what you want it do.

I used the concept of creating a mapping table with Period->Prior Year Period->Prior Period and used joins to create associations.

Let me know if that's helped.

M.

[thenning]

Hi @mceleavey,

Yes, this is helpful.  One thing, I have to do the same breakout on the quarterly side (that you did on the monthly); how would I get that to work?

T.

[mceleavey · Accepted solution]

Cheeky ;)

There you go.

[aetingu]

Hi,

I know this question was answered, but I would like to provide another solution.  Perhaps others searching for similar solutions can consider.

I found that doing historic calculations such as Month on Month, Year on Year, Year to date very difficult with what is available in Alteryx.  It is definitely doable but takes a lot of steps to achieve it (my personal view).  So I found that using Python would make it a lot easier to do this sort of calculations as it would literally require you 2-3 lines of code.

This example is more towards calculating 12M Growth Rates, but you can utilize Python's pandas package to just change the function to whatever you desire (rolling function for rolling averages and means etc by the window of your choosing).

I assume that you have some sort of input data (excel, csv, sql etc).  All you would need to do is to pass it on to a Python Tool and let python do the heavy lifting for you as such:

I hope this might be of use to people.

Regards,

Aksel

[MikeN]

@aetingu - very helpful Python!

How would you add a "grouping" to this code?

i.e. you wanted to run this calculation for, say, the US...and then the next row start up again for the UK.

Like the "check box" in the Multi-Row tool in Alteryx allows you to do a group-by, so the calculations start afresh.

[aetingu]

@MikeN Do you mean grouping within Python or for the input.  I.e. Here it is using UK Balances against UK GDP, and you want to use US Balances vs US or UK GDP?  If so you can use a dynamic input tool to loop though your input and append the result.

[MikeN]

Hi - I mean within Python.

So if I have 5 rows of data for the UK, and then 5 rows for the US, I would need this calculation to stop when it reaches the end of the UK data, and start again with the US.

Does that make sense?

[aetingu]

@MikeN It depends what you end goal is.  I can think of two quick way of doing this:

Option one: 

When you import the data from Excel etc, you can Transpose the data, so UK and US would have their own columns.  So the dataframe coming into Python Module is Date UK US (as columns).  Then within your python you can apply a simple formula for a new column where you label it as 12M_UK_Growth.

a['12M_UK_Growth'] = (a['UK']/a['UK'].shift(12)-1)
a['12M_US_Growth'] = (a['US']/a['US'].shift(12)-1)

Option two:

If you don't want to transpose the data in to columns, then:

a['12M_Growth_Rate'] = a.sort_values('Date').groupby(['COUNTRY']).Value.pct_change()

If your work heavily relies on Python with additional libraries that are not in Alteryx, you can do what I do.  Code all in python, convert it to exe (pyinstaller) then just call the exe file from within python in Alteryx.

Hope it helps.