Description:
I’m facing an issue with restructuring my transactions dataset in Causal.
I’m seeking help to understand the problem and find a solution to format the data correctly.
Currently, when I import the dataset into ‘Causal,’ the table does not match my desired output.
Dataset Example:
The dataset I have consists of the following columns: Date, Country, City, and Total Gross Revenue.
Here’s an example of the data:
Date
Country
City
Total Gross Revenue
2023-04-30
Australia
ADLCity
4677.59
2023-04-30
Australia
Albany
378.67
2023-04-30
Turkey
Ankara
3032.17
2023-04-30
Turkey
Antalya
2388.75
Expected View in Causal:
I want to present the data in a specific format, where the headers represent dates, and the data is grouped by country and city. The desired output is as follows:
Country
2023-04-30
Australia
ADLCity
4677.59
Albany
378.67
Turkey
Ankara
3032.17
Antalya
2388.75
Current View in Causal:
However, the current output in ‘Causal’ does not match the desired format. The data is duplicated and does not group the information correctly:
Country
2023-04-30
Australia
ADLCity
4677.59
Albany
378.67
Ankara
3032.17
Antalya
2388.75
Turkey
ADLCity
4677.59
Albany
378.67
Ankara
3032.17
Antalya
2388.75
Seeking Assistance:
I would appreciate guidance on why the data is duplicating in Causal and how I can restructure it to achieve the desired format.
Hey @sarviinageelen - Country and City need are two separate categories that relate to each other, i.e. every City sits within a Country. If you haven’t linked them together though, Causal does not understand this inherently!
So in order for Causal to understand this relationship, you need to link them together. The best way to do this is to adjust the column header in your google sheet from “Country” to “City > Country” (the “>” representing the mapping).
Once you’ve done that, you should refresh the data source in Causal, and you’ll then see in your data table that each City maps to a Country. Example of what this would look like in screenshot below
Then you’ll see your Revenue variable broken down by City only, and in a separate variable can group Revenue by City, by Country instead Unfortunately you can’t see them both at the same time right now in one variable (coming soon).
Its also worth noting that in your screenshot you’ve explicitly applied the categories on the Revenue variable. You should only do this if you want to set a different value for each category item. In this case, your Revenue variable should just inherit the categories from data. See more on explicit vs. inherited/implicit here.
Hello @heidi_causal, thank you for your assistance with the workflow.
I would like to provide an update on the changes I made to my Google Sheet and the issue I encountered while trying to group the data.
I made a modification to the column header in my Google Sheet. Instead of “Country,” I changed it to “City > Country.”
As a result, I was able to visualize the data in a table format, where each city was mapped to its corresponding country.
Next, I created a variable for revenue, but it seems to be broken down by all cities rather than aggregated by country.
My objective was to group the cities based on their respective countries. However, I encountered an issue where the option to group the data by cities was disabled or greyed out.
Consequently, I’m seeking guidance on how to group the Revenue by cities first, and then have those cities nested under their respective countries.
Additionally I have recorded my screen with voiceover to help out where I am stuck at here
Please let me know if there’s anything else you need clarification on or if you require additional information.
The missing step is to transform the data item! Please see docs here. If you select the data icon on the variable you will be able to group by the Country Category since you have now created this mapping. That should give you the desired result.
Hey @sarviinageelen - please see this comment from Heidi above. It’s currently not possible to see both breakdowns in the same variable. You could have them in separate variables or you could put the variable broken down by City into a Table (see docs) and if you added a Country filter, you could see the different breakdowns this way.
Unfortunately you can’t see them both at the same time right now in one variable (coming soon).
