Excel Pivot Chart like output

[EricO]

Is there any way to create a chart with multiple row/column group by functions like in Excel using the Interactive Chart tool.  I want to show year over year results of responses received to a survey by date. as in the chart below.

My Source data looks like this:

Date	Count
1/1/2015	37
1/2/2015	28
1/3/2015	47
1/4/2015	4
11/30/2015	1
12/1/2015	1
12/13/2015	2
12/14/2015	7
12/15/2015	3
12/17/2015	2
12/18/2015	1
12/20/2015	146
12/21/2015	52

[AngelosPachis]

Hi @EricO 

You can create that with the Interactive Chart tool, but you should do some data prep first. Use a formula tool to bring your date in a format that Alteryx can understand as a date, and then parse out the Year and the Month-day info.

The former will be used to colour your different lines and the latter as you x-axis.

Then in your interactive chart tool, plot the line chart as shown below :

and under the transforms tab, select to split by year

Then you should end up with something looking like that:

Hope that helps, let me know if you have any questions.

Regards,

Angelos

[EricO]

Hi Angelos,

Thanks for your help.  Your input has gotten me 90% of the way there.  One small issue I am having though, is that my data for each series crosses the December/January boundary, and the Interactive chart tool can't figure out how to sort the x-axis. I played with presorting the data by using an artificial julian date were days < 100 get 366 added to them which fixed the individual plots, 

When only one series is graphed, it works fine

When more than one series is plotted however, the data starts to get scrambled.

Note the out-of-order Jan,Nov, Dec data points for the 2015 dataset in the graph above.  Any idea on how to deal with this?

[AngelosPachis]

Hi @EricO ,

So out of curiosity, the January shown in your graph coming after December, is it still 2015 or is it 2016? 

[EricO]

The date is really in 2016, but is part of the 2015 campaign, so 2015 is really the series name.

[AngelosPachis]

Hi @EricO ,

I've made some small changes and amended the year in the date fields (so Jan in the 2016 campaign would be 2017).

Then, I've created a new field/flag called "campaign", which I've used to split and colour the different lines. 

Then it was just a matter of parsing the date and sorting the fields as needed.

Hope that helps, let me know if it worked for you.

Regards,

Angelos

[EricO · Accepted solution]

Hi Angelos,

Thanks for your help.  This was more complicated than just sorting the data properly.  The underlying issue was that each "Campaign" or series as they are called in Excel needed to have all the same Range values (Date) present in order for the chart tool not to go crazy. In your examples, you used data points on all the same day-of-month, even if you skipped some days.  However, in my actual data, not every series had the same calendar days.  For example in 2015 there might have been a Nov 15, but in 2016-2020, there might not have been.  Once I created a Cartesian table of dates, so that every series has the same number of range values, the chart sorted itself out.  There is probably an easier way to do this, but it was what I came up with base on my limited experience with the tool.  Thanks again for the help.

 vs. 

[AngelosPachis]

Hi @EricO ,

Apologies for that, I did all I could with the data and knowledge of the problem you provided.

Glad you manage to sort it out on your own.