Re-Organizing Data - Charts - 12 Month Rolling Average

Hello everyone,

I'm still a "newbie" in terms of setting up Alteryx workflows, and the one I'm currently working on is the most complex one I've done to date, so I'm hoping to get some assistance with a workflow I'm currently working on. I started with the raw data (see attached) and was able to some calculate percentage changes, etc. (see attached output). I need to turn this data into a chart, but I think I need to re-configure the data so that the quarters are the column headers and there are separate rows for the "Lives", "% Increase Prior Quarter", & "% Increase PY Qtr" columns from the output. I've tried the Arrange, Transpose, & Cross Tab tools, but it's not coming out how I expected. I also thought about going back and transposing the data from the beginning, but then the calculations don't work correctly. I'm sure I'm missing something, so any assistance would be greatly appreciated.

Also, since I've mentioned the chart, is there a way I could configure a chart in Alteryx to have the total enrollment (in the attached) as a bar chart and input my company's enrollment as a line chart?

Finally, I'm also looking to calculate a rolling 12-month average (on a different input). Is there an easy way to accomplish this?

Again, I would appreciate any help.

Thanks!

-T

Raw Data for Calculations.yxmd

Calculations.csv

Reporting

Accepted answers

wdavis

Hi,

No problem, it is a slightly longer process to produce this table. The attached workflow should give you the information on how to get the data in the format you desire.

When you open the workflow you will need to update the input data tool to your correct file location. I couldn't see the field for Company Lives within the Calculations CSV file, so you will just need to amend the configuration in the first Transpose tool to reflect this.

You can then output this to your desired location.

Any questions, let me know.

Regards

Will

MarketLivesWorkflow.yxmd

All comments

wdavis

Hi @thenning

I think you have your data formed in the correct way, it may just need to be cleansed slightly. What are you hoping to show on each of your charts? Which of the fields you have created will be shown on each chart? I can then help you get these built.

For your second question, yes this is possible. If you are using the Interactive Chart Tool then you will notice that their is the option to 'Add Layer', this will enable you to have your total enrollment as layer 1, with 'Type' set to a Bar chart and Company enrolment as layer 2, with 'type' set to Line chart. See screen grab below.