Arrange data for stacked bar chart

I have a large data set where the column headers are a date with statuses (represented with letters below) under each date. The number of weeks/columns and the potential statuses are both infinite and the workflow needs to dynamically include all.

I'm stuck on how to arrange the data to create a stacked bar chart with the dates along the x-axis, with the chart showing the count of each status for each of these weeks.

01/01/2019	01/08/2019	01/15/2019	01/22/2019	01/29/2019	02/05/2019
A	A	A	B	B	B
		C	C	C	C
	A	A	B	B	B
		D	D	E	E
B	B	B	C	C	C
	F	F	F	F	G
					F
			D	E	E
	E	E	E	D	D

Reporting

Preparation

Accepted answers

danilang

Hi there @AmandaH

Here's what you're looking for.

After cleaning the data, transpose it to get all the values in name value pairs. Count the statuses grouped by date and status. Crosstab after removing the null values. The interactive chart config tool allows to add the series and then change the Layer style to Stack.

Results

Note that when your building the configuration in the Interactive Chart tool, the graph displayed in the example uses built-in data and not your actual data so the values won't necessarily match up when your configuring. You have to run the workflow and render the graph to get the final output

Dan

Stacked.yxmd

All comments

danilang

Hi there @AmandaH

Here's what you're looking for.

Results

Dan

Stacked.yxmd

AmandaH

Thank you @danilang !!

Figured out where I was going wrong. This worked perfect. Thank you again!

VegasBeans

This was helpful because I was previously using the Split feature within the Interactive Chart tool, but couldn't find a way to sequence the stacked bars in the desired order. Using the Crosstab tool prior allowed me to set the desired sequence. When I tried eliminating the Crosstab tool in your example and using the Split function, the bars didn't come out alphabetically, but rather based on first 'appearance' in the data (ordered by date). Would definitely like to see an enhancement that enables the following:

1) Assign sequence of Layers directly in the Interactive Chart tool

2) Ability to rename Layers within the Interactive Chart tool (for purposes of hover-over and Legend)

3) Ability to change the "Bar Mode" (i.e. stacked / group / overlay) between layers. (I'd like to use Group in one layer and Stacked in another)

I'd welcome any feedback on the above requests - thanks for putting this together.

Ben

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335