Challenge #229: Trend Analysis

A solution to last week’s challenge can be found here.

This week's challenge was submitted by @wwatson - Thanks for your submission!

Two sets of performance data are provided below. Plot that data on bar charts, calculate a trendline for each graph, and export the charts to a single Powerpoint slide.

The method for calculating the trend line can be found on various websites. The author used this one.

challenge_229_start_file.yxzp

Trend Line Analysis.pptx

challenge_229_solution.yxzp

Reporting

Advanced

Intermediate

Accepted answers

All comments

Aaron_Harter

Not your average challenge!

Spoiler

(edit 8/17 9:53 CT) video of solution overview:

Spoiler

The trend line calculation was driven by the core logic from this Macro in the gallery - https://gallery.alteryx.com/#!app/Least-Squares-Regression--Slope--Intercept-/5ab4ff0f8a9337138c471ab7

challenge_229_AB Data_Aaron.yxzp

dsmdavid

Not Base Alteryx compliant, but...

Spoiler

david_229.yxmd

johnemery

Fun challenge. A good one, too, for those who aren't well-versed in linear regression: this is how it's done!

But tisk tisk tisk; shame on you Alteryx for truncating the y-axes on your output charts.

Spoiler

Check out my solution video below:

Spoiler

Challenge 229 Solution jemery.yxzp

Blake

229 done!

Spoiler

challenge_229_start_file.yxzp

atcodedog05

@Aaron_Harter Great to know about the macro.

wwatson

Extra points for those not using the macro though 🙂

atcodedog05

Was a great exposure : )

Thank you for this great challenge @wwatson

I followed the steps from the website given in the challenge description.

Spoiler

Cheers and Happy analyzing : )

challenge_229_atcodedog05_solution.yxmd

patrick_digan

Spoiler

challenge_229_start_file.yxmd

jasperlch

Spoiler

challenge_229_JL.yxmd

DaisukeTsuchiya

Done with Formula. But Tableau is much easier...

Spoiler

challenge_229_start_file_DT.yxzp

etzele

Never thought about exporting my workflow to PowerPoint, but good to know it works!

Spoiler

challenge_229_solution.yxmd

Kenda

Spoiler

challenge_229_start_file.yxmd

philipmannering

Yes, I also cheated with the Python tool.

Spoiler

Report

Trend Calc

Workflow

Challenge 229 - Trend Analysis.yxmd

Spalders

Spoiler

A bit of a clunky solution cutting and pasting the Trendline calculation for each data set

challenge_229_ADS.yxmd

phottovy

My solution:

Spoiler

challenge_229_ph_solution.yxzp

OllieClarke

I matched the styling fairly closely, but I didn't truncate the axes as it is definitely not best practice with bar charts...

Spoiler

Workflow

Output in ppt

challenge_229_OC_solution.yxzp

hanykowska

Nice!

Spoiler

At first, I thought about using predictive tools - linear regression in particular but couldn't get the line information out of it. So I solved it implementing the trend line calculations as attached to the challenge. then I went back to predictive and used the score tool to predict the values from the linear regression which gets the same result but with my custom macro I can easily write down the calculation for the trend line

Trend line implementation:

challenge_229_solution_hanykowska.yxzp

LHolmes

Super challenge! Really enjoy the mathematical analysis. Always get new ideas and learn new ways to configure when playing with reporting tools.

Spoiler

challenge_229_solution_lmh.yxzp

RolandSchubert

Spoiler

My solution.

challenge_229_solution_rsc.yxmd

abrouwer

See the solution attached.

challenge_229_solution_file - AJB.yxmd

balajilolla

Spoiler

Solution Attached

challenge_229_start_file.yxmd

paulfound

Spoiler

Bit repetitive could join them together i guess to make more slim-lined or make into a macro :S

challenge_229_PF.yxmd

mustel

Am I really the only one who cheated and used the linear regression model!?!

Spoiler

Also, shame the Visual Layout (beta) doesn't allow you to centre an object yet...

challenge_229.yxmd

dlopez13

Certainly makes you appreciate the Linear Regression predictive tool macro!

Spoiler

challenge_229_start_file.yxmd

mustel

And now my "hack" is completely dynamic 🙂

Spoiler

Just had an idea to utilise the score tool instead of using the coefficients in a formula

challenge_229.yxmd

Tony_Castillo

Spoiler

challenge_229_solution_TC.yxmd

Qiu

I am not sure this challenge shows the power of Alteryx since the simple Excel can do the same.
Though I practiced the Macro and Reporting tools, which is good for me.

Spoiler

challenge_229_start_file-Qiu.yxzp

jeng

Spoiler

Doing the math inside a macro:

Using it to get the individual trendlines, apply chart formatting and assemble output:

CalculateTrendline.yxmc

challenge_229_jeng.yxmd

JennyMartin

Spoiler

Great challenge for dusting off stats skills and the reporting tools. Video solution here: https://www.youtube.com/watch?v=xiWW54PIqBo&list=PL18SpOXDj-KMUD4YwWC5vZaUsIa7drxNZ&index=34&t=0s

AWC229.yxzp

NS4

Interesting one

Spoiler

Sashikumar

Good to work with calculation stuffs.

challenge_229_start_file_sk.yxmd

Verakso

Thanks to @wwatson for a fun challenge.

I will for sure, think about the math behind, next time I just add a trendline in a Viz tool.

Spoiler

This is how my challenge ended up, I thought as others to use Python or take a different approach, but sometimes plain calculation and interstation can work out as well

The Workflow

I think my result did match the provided PowerPoint, but I am still struggling with the Alteryx Report Tools, since I find them a bit tricky.
The Powerpoint

Still Climbing
/Verakso

challenge_229_solution_verakso.yxzp

Trend Line Analysis.pptx

jiachern

Pretty fun one to do

challenge_229_start_file (JC).yxmd

Carolyn

Solved - a bit rusty on my Algebra

Spoiler

2025-02-20 09_58_10-Alteryx Designer x64 - challenge_229_start_file.yxmd.png

challenge_229_Carolyn.yxzp

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335