Trend Line Slope

[danchips24]

Hi, I am trying to figure out how to manually calculate the slope of a trend line in Alteryx. I am looking at a list of supplier purchases and need to identify suppliers that have negative purchasing trends (i.e. throughout the year our company has started to purchase less and less from the supplier either quarter by quarter or month by month). My plan is to use the calculated slope to filter a list of all suppliers and identify which suppliers have negative slopes, indicating any supplier with a negative purchasing trend throughout the year. Obviously I can figure this out for one supplier but I need a way in Alteryx to solve for over 200 at once in order to target specific suppliers. Please let me know if anyone has any insights. Thank!

[JessieC · Accepted solution]

@danchips24 - You can examine the trend using Scatterplot or TS Plot for time series data.

Using this for slope...

I built this example to calculate slope where Y is purchases and X is time (months) - 

[danchips24]

I think this definitely works on a certain level, but the data I am looking at has fairly complex trends over time. As such looking solely at the first and last data points of each set does not present an accurate representation of the trending for each supplier.

[mceleavey]

Hi @danchips24 ,

Can you post the data with any identifying fields removed?

[estherb47]

Hi @danchips24 

Wondering if this macro would be helpful. https://gallery.alteryx.com/#!app/Least-Squares-Regression--Slope--Intercept-/5ab4ff0f8a9337138c471a...

There are macros available to pull the regression calculation from a linear regression tool as well. Take a stroll through the public gallery to find more.

Let me know if that helps!

Cheers,

Esther

[danchips24]

Just wanted to follow up and mention that I did end up using this approach but instead of just looking at beginning to end, I had the summarize tool drill down further to show week by week purchases. This allowed for the slope calculation to be performed for every week throughout the year. Then by averaging the slopes throughout the year I was able to come up with a reasonable trend line for each supplier and filter by suppliers that had negatively trending slopes.

Thanks for the help!

[JoshuaM]

Hi @danchips24,

I was doing a similar exercise and while searching came across this thread. I know you found a solution but thought I'd share mine as well in the event others are doing this same task. 

I ended up building a macro that calculates the slope of two points based on a given interval and date format (week, month, quarter, year). For example, if the users specifies that they would like 3 weeks or 4 months, the slope will calculate based on those selections. You also have the ability to run this on multiple metrics if for instance you had Sales and Volume. Lastly, the macro is configured as a batch macro where the control parameter can be specified as Company, allowing a group by of that field.