Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Re-Organizing Data - Charts - 12 Month Rolling Average

thenning
7 - Meteor

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

 

 

5 REPLIES 5
wdavis
Alteryx
Alteryx

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.

 

Regards,

Will

 

Chart tool.PNG

thenning
7 - Meteor

Hi @wdavis,

 

I've attached a snapshot of the chart I'm trying to create in this workflow.  Here are 2 charts that I'm ultimately trying to create:

 

  1. Lives (bar chart) and Company Enrollment (line)
  2. Company Enrollment (bar chart) and % Increase Prior Quarter

Thank you for your help.

 

Sincerely,

-T

 

Sample Chart.JPG

wdavis
Alteryx
Alteryx

Hi @thenning 

 

Unfortunately it is not currently possible to configure the chart as you have displayed below with the secondary axis within the Alteryx Designer. The additional 'Layers' I mentioned previously will only work across a single axis, so you would need the 2 fields on each chart to be the same or similar values.

 

If you do require any more information using the charting tool, please refer to this attached article which provides lots of other helpful information - https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Interactive-Chart/ta-p/250299

 

Kind Regards

Will

thenning
7 - Meteor

@wdavis 

 

Thanks for the information Will.

 

Since I cannot create the chart within Alteryx at this time, I would like to re-arrange my output so that it can easily go into Excel and I can easily update the charts I need.  My desired format is outlined below.  I've tried a few things with the Transform tools, but it's not coming out how it should.  Can you please help?

 

 FY17Q1FY17Q2FY17Q3FY17Q4FY18Q1FY18Q2FY18Q3FY18Q4FY19Q1
Market Lives         
Company Lives         

 

Thanks again for all of your help.

 

-T

wdavis
Alteryx
Alteryx

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

Labels