Combining Dimensions to give a single dimension for grouping
Hi everyone,
I am stuck with a categories only data problem and I wondered if you could help me, please?
My database has the following fields:
USER ID
DRIVE TIME (LAST YEAR)
DRIVE TIME (CURRENT YEAR)
TRAIN TIME (LAST YEAR)
TRAIN TIME (CURRENT YEAR)
Where besides USER ID, the other columns can assume any of the text values as following, being travel time periods:
1 - 10MIN
11- 20MIN
21 - 30MIN
31 - 40MIN
41 - 50MIN
50MIN +
I attached a photo of the dataset format as an example.
So user 1 for example, can have a DRIVE TIME (LAST YEAR) = 1 - 10MIN but DRIVE TIME (CURRENT YEAR) = 11- 20MIN.
My visualisation goal is to show a graph where we have two bars (DRIVE TIME (LAST YEAR) and DRIVE TIME (CURRENT YEAR), grouped per each time period. Example:
Example
Or a table showing similar information (example attached). However, so far the table was breaking into combinations of time travel such as 1 - 10MIN, and all the time travel repeated for the second column, doing a COUNT of USER ID on each of the combinations possible. But that is not what I want.
Moreover, I should be able to filter on each travel time period on the bar graph for example, and having the entire dashboard readjusted.
PS: because of many reasons, I do not want to use the script to solve it, please.