Forum Discussion
Clustered Stacked Column Chart: Segmented Analysis
Hi! I want to create one chart that would work with data with the following columns:
1st Column: Time from A to C
2nd Column: Time from A to B
3rd Column: Time from B to C
For my visual, I want to break the chart out into 4 categories accross the X-axis: Mean, Segmented Mean, 95th%, and Segmented 95th%. The Y-axis will merely be the accumulating time.
The Mean and the 95th% categories would present data from 1st column.
The Segmented Mean and Segmented 95th% would present data from the 2nd and 3rd column, but stacked.
It would look something like this:
The problem that I have encountered thus far is that I can't get the Mean and 95th% to be pulled out as their own category. Instead, the Mean gets lumped in with the Segmented Mean Values. I have tried to create columns with labels (Mean, Seg Mean, 95th, Seg 95th), but that does not help since all of the data is being used. I have tried to create measures and drag the measures in, but that does not seem to help.
Is there a way to make this work...without creating 4 separate charts?
By the way, I realize that I could specifically use a waterfall chart, but I am hoping not to do that.
Thank you in advance for any help!
I am happy to report that I solved this. The way I approached it involved much work. Maybe there is a better way. Please share if you find a way.
1. I took my primary query with the calculations (e.g. A to C, A to B, and B to C) and duplicated (or referenced) them about 4 times, naming each of the duplicates as follows: Mean, Mean Segmented, Percentile, Percentile Segmented.
2. For each of the queries, I created a Category Field and named them accordingly (Mean, Mean Segmented, Percentile, Percentile Segmented).
3. For the Mean and Percentile Queries, I ripped out the calculations "A to B" and "B to C". Also, in each query, I relabeled the "A to B" calculation. For example, in the Mean Query, I labeled the column as "A to B Mean" and in the Percentile query, I renamed it as "A to B Percentile.
4. For the queries Mean Segmented and Percentile Segmented, I ripped out the "A to C" calculation column. Then, I renamed the remaining calculation columns, similar to the method I used in Step 3.
5. I duplicated the Mean Query and then renamed it "Combo".
6. I appended the following queries to the Combo Query: Mean Segmented, Percentile, Percentile Segmented.
7. Close and Save.
8. I located the following in the fields pane: "A to C Percentile", "A to B Percentile", and "B to C Percentile". For each of these, I created a measure to calculate the 95th percentile.
9. In the Visualization Pane, I chose stacked column chart.
10. I drug "Category" from the Combo query into the AXIS field.
11. I drug the following fields into the VALUE field and set them to AVERAGES: A to C Mean, A to B Mean, B to C Mean.
12. I drug the following MEASUREMENT fields into the VALUE field: A to C percentile, A to B percentile, and B to C percentile.
I hope this helps!
1 Reply
I am happy to report that I solved this. The way I approached it involved much work. Maybe there is a better way. Please share if you find a way.
1. I took my primary query with the calculations (e.g. A to C, A to B, and B to C) and duplicated (or referenced) them about 4 times, naming each of the duplicates as follows: Mean, Mean Segmented, Percentile, Percentile Segmented.
2. For each of the queries, I created a Category Field and named them accordingly (Mean, Mean Segmented, Percentile, Percentile Segmented).
3. For the Mean and Percentile Queries, I ripped out the calculations "A to B" and "B to C". Also, in each query, I relabeled the "A to B" calculation. For example, in the Mean Query, I labeled the column as "A to B Mean" and in the Percentile query, I renamed it as "A to B Percentile.
4. For the queries Mean Segmented and Percentile Segmented, I ripped out the "A to C" calculation column. Then, I renamed the remaining calculation columns, similar to the method I used in Step 3.
5. I duplicated the Mean Query and then renamed it "Combo".
6. I appended the following queries to the Combo Query: Mean Segmented, Percentile, Percentile Segmented.
7. Close and Save.
8. I located the following in the fields pane: "A to C Percentile", "A to B Percentile", and "B to C Percentile". For each of these, I created a measure to calculate the 95th percentile.
9. In the Visualization Pane, I chose stacked column chart.
10. I drug "Category" from the Combo query into the AXIS field.
11. I drug the following fields into the VALUE field and set them to AVERAGES: A to C Mean, A to B Mean, B to C Mean.
12. I drug the following MEASUREMENT fields into the VALUE field: A to C percentile, A to B percentile, and B to C percentile.
I hope this helps!