The bars on the vertical bar graph I want to create is an aggregation that needs to be split by some fields. Here’s an example of how the raw data looks:
---------------------------------------------------------------
| ID | State1 | State2 | Param1 | Param2 |
---------------------------------------------------------------
| 1 | True | False | A | X |
---------------------------------------------------------------
| 2 | False | True | B | X |
---------------------------------------------------------------
| 3 | False | True | B | Y |
---------------------------------------------------------------
| 4 | True | False | A | Y |
---------------------------------------------------------------
I created a bar graph that sums State1 and State2 as values, which creates 2 bars - one for each state column. But what I really need is one bar for each state + Param1 + Param2 combination, so for example, in the table above, these would be all the bars:
State1 = True, State2 = False, Param1 = A, Param2 = X
State1 = True, State2 = False, Param1 = A, Param2 = Y
As a note, when State1 is true, then State2 is false, and vice versa. There are more than just 2 states in my real data, but I’ve made it in this way so I can count the rows for each state (they’re calculated based on other columns in the data). But each state also needs to be split by every combination of Param1 x Param 2, which I thought I could do with a y-axis, but it seems to be missing.
Is there an easy way to accomplish what I’m trying to do?
Hello,
I am trying to understand the use case to provide the correct answer.
What would be the values that you want in X axis and what are the values that you want in the cluster (grouped per each value in X Axis) and finally what is the Y-axis value that you want to measure?
Thank you
The X-axis is the date. The raw data has the date of when the data from that row was collected.
The Y-axis is Param1 x Param2. That is, if Param1 can take the values “A” and “B”, and Param2 can take the values of “X” and “Y”, then the values of the y-axis are “A-X”, “A-Y”, “B-X”, and “B-Y”.
The values are the sums of each state column. That is to say, it counts the number of rows that have “State1” or “State2” as true. If these states were, for example, “Underweight” and “Overweight”, then the values are simply how many people are underweight or overweight.
(Note: my data isn’t about people’s weights, but it seems to be a good explanatory device)
What I would like is to be able to split the values by the y-axis. Right now, each value is represented by one bar only - ie, everyone who is “Underweight” is treated as one blob.
Continuing the weight analogy, if Param1’s values were “Sedentary” and “Active”, and if Param 2’s values were “Tall” and “Short”, then I’d like to be able to split the bar for “Underweight” and “Overweight” into the following breakdowns for each day/x-axis value:
“Underweight-Tall-Sedentary”
“Underweight-Tall-Active”
“Underweight-Short-Sedentary”
“Underweight-Short-Active”
“Overweight-Tall-Sedentary”
“Overweight-Tall-Active”
“Overweight-Short-Sedentary”
“Overweight-Short-Active”
The data from our query supports deriving these breakdowns, but it is already pretty long and complicated, and I would have liked to preserve the raw data’s structure/format as is by using the x- and y- axes in QuickSights to automatically derive the breakdowns instead.
create a calculated field like the one below that will concatenate all the dimensions you want as a part of your grouping. In your original example, the resulting sting from this calculated field for ID 1 would be “True-False-A-X”. Use this in the “Group/Color” field well.
create a distinct count of that dimension, to use as your Value.
Also, I tried removing all the values to start again and perhaps get to your configuration, but there is no pencil icon for me to write a string in any of the field wells.
You will need to build the calculations by going to the top left corner and choosing the option to create a calculated field. After creating the field, you can then add it to the field well. Hope that helps!
