How to display 0 for missing records while blending data Google Data Studio?
I am attempting to blend two data sources in Google Data Studio:
Data Source 1. weekly sales numbers by market (example below)
| ID | WeekEndingDate | Market | Sales |
|---|---|---|---|
| 1 | 7/17/2022 | Utah | $400 |
| 2 | 7/17/2022 | Arizona | $500 |
| 3 | 7/17/2022 | Colorado | $600 |
| 4 | 7/17/2022 | Dallas Fort Worth | $700 |
| 5 | 7/10/2022 | Utah | $400 |
| 6 | 7/10/2022 | Colorado | $300 |
| 7 | 7/10/2022 | Dallas Fort Worth | $800 |
| 8 | 7/10/2022 | Arizona | $600 |
| 9 | 7/10/2022 | Utah | $500 |
| 10 | 7/10/2022 | Colorado | $100 |
Data Source 2. weekly budget by market (Example below)
| WeekEndingDate | Market | Sales Budget |
|---|---|---|
| 7/17/2022 | Utah | $5000 |
| 7/17/2022 | Arizona | $4000 |
| 7/17/2022 | Colorado | $3000 |
| 7/17/2022 | Dallas Fort Worth | $2000 |
| 7/17/2022 | Tennessee | $1000 |
| 7/17/2022 | Oregon | $1000 |
| 7/10/2022 | Utah | $5000 |
| 7/10/2022 | Arizona | $4000 |
| 7/10/2022 | Colorado | $300 |
| 7/10/2022 | Dallas Fort Worth | $2000 |
| 7/10/2022 | Tennessee | $1000 |
| 7/10/2022 | Oregon | $1000 |
What I Want in Data Studio Table (Example below) I have bolded the rows that I am unable to get in my table. The rest of the rows I am able to get perfectly.
| WeekEndingDate | Market | Sales Budget | Sales |
|---|---|---|---|
| 7/17/2022 | Utah | $5000 | $400 |
| 7/17/2022 | Arizona | $4000 | $500 |
| 7/17/2022 | Colorado | $3000 | $600 |
| 7/17/2022 | Dallas Fort Worth | $2000 | $700 |
| 7/17/2022 | Tennessee | $1000 | $0 |
| 7/17/2022 | Oregon | $1000 | $0 |
| 7/10/2022 | Utah | $5000 | $900 |
| 7/10/2022 | Arizona | $4000 | $600 |
| 7/10/2022 | Colorado | $3000 | $400 |
| 7/10/2022 | Dallas Fort Worth | $2000 | $800 |
| 7/10/2022 | Tennessee | $1000 | $0 |
| 7/10/2022 | Oregon | $1000 | $0 |
My blend in data studio
What I am able to generate in Data Studio Please see this data as a reference for the screenshot below https://docs.google.com/spreadsheets/d/1nZbuX7x--BoO63sDmv4b3NaeL3ixaInl4IWsyXfnLLo/edit#gid=1617242817
I have successfully created an inner join blend between the two data sources linking the week date and the market. The data is pulling almost perfectly and I am like 98% done, however, what I am having trouble with is that data source #2 has weekly budget records for weeks that do not exist in data source #1 and those weeks will not show up on my table in data studio (ex. week ending 7/17/2022 for Tennessee budget is $2,000 but no sales were made in week ending 7/17/2022 in Tennessee, therefore, there are no records of sales for week ending 7/17/2022 in data source 1).
I currently have in a simple table each weeks sales and budget in two different columns but week 7/17/2022 is missing because there are no records for week ending 7/17/2022 in data source 1 to match the week in source 2. I need to display that week 7/17/2022's budget for tennessee is $2,000 and that there were $0 in sales for that week. Does anyone have an ideas on how I can accomplish this?
PS - I attempted a full outer join of the two data sources and there was no difference.
UPDATE - sorry for the lack of information at first. I have compiled some data that will hopefully help clarify this issue and you can find the data here: https://docs.google.com/spreadsheets/d/1nZbuX7x--BoO63sDmv4b3NaeL3ixaInl4IWsyXfnLLo/edit?usp=sharing
You can also find a sample data studio report that you can view how I have it currently setup based on the sample data in the google sheet link above: https://datastudio.google.com/reporting/adfc3ee5-6a9f-4c57-b66f-cff27e91850e
I have also added some sample tables directly on Stackoverflow for a quicker sample view.
0) Summary
- Looks at the issue
- Solution #1 - Left outer join
1) Background and Issue
To visualise the issue and let's focus on the values where WeekEndingDate is Jul 17, 2022 and look at the values in the Market field:
| WeekEndingDate | Market | Data Source 1 | Data Source 2 |
|---|---|---|---|
| Jul 17, 2022 | Utah | ✅ | ✅ |
| Jul 17, 2022 | Arizona | ✅ | ✅ |
| Jul 17, 2022 | Dallas Fort Worth | ✅ | ✅ |
| Jul 17, 2022 | Colorado | ✅ | ✅ |
| Jul 17, 2022 | Oregon | ✅ | ✅ |
| Jul 17, 2022 | Nevada | ❌ | ✅ |
| Jul 17, 2022 | Tennessee | ❌ | ✅ |
As indicated by the ❌, two Market values (Nevada and Tennessee) are available in Data Source 2 but not in Data Source 1
2) Suggestion #1: Left Outer Join
Shift the data sources so that data source 2 is on the left and data source 1 is on the right and then left outer join:
2.1) Blend Fields
| Description | Table 1 | Table 2 |
|---|---|---|
| Data Source: | 2 | 1 |
| Dimension 1: | WeekEndingDate |
WeekEndingDate |
| Dimension 2: | Market |
Market |
| Metric 1: | Sales Budget |
Sales |
| Date Range: | WeekEndingDate (Auto) |
WeekEndingDate (Auto) |
| Image: |  |
 |
2.2) Join Configuration
| Description | Table 1 🔗 Table 2 |
|---|---|
| Join Operator: | Left Outer |
| Join Condition 1: | WeekEndingDate (Data Source 2) 🔗 WeekEndingDate (Data Source 1) |
| Join Condition 2: | Market (Data Source 2) 🔗 Market (Data Source 1) |
| Image: |  |
Publicly editable Google Data Studio report (embedded Google Sheets data source) to elaborate:
- Strange behaviour of Functions with Dates
- How to apply RLS to other dimensions when using the static RLS approach?
- Power BI calculate Measure for previous working day
- Calculated table with aggregation DAX
- How does dax week and year handle 31 dec 2023?
- How to ETL my PostgreSQL data into a ClickHouse datawarehouse?
- How to get month name from month number in Power BI?
- Is there a way to group by intervals of 15 min in DuckDB?
- Can a Superset dataset use multiple data sources?
- Google Data Studio Sum with Conditions
- Power BI in Office 365
- How do you create a parameter list drop down in Power BI?
- How to create a table from another table in PowerBI and Sum a column
- How to change the date format in Oracle BI Publisher?
- Export dashboard and chart into PDF (Apache Superset)
- Open existing reportings services in Business Intelligence Development Studio
- PowerBI remember filters when opening new report from another report
- Many-to-many relationship database design
- Difference between Fact table and Dimension table?
- How to create a measure to get the maximum or minimum value from all the rows from a table in Power BI by filtering a column?
- QlikSense, how to make a piechart where the value for the pie chart is the sum of multiple summed columns, and the slices are the columns
- Difficulty modeling Star schema
- Unable to connect Metabase with Oracle Database
- Migrating from ADF to SSIS
- BI: Dedicated date tables for each date column in a related dimension table
- Match multiple occurences of string A in string B?
- Matching string for patters via MATCH?
- Data Modeling with a Time Dimension
- How to format a date in Power Query? (Microsoft Excel 2016 - Query Editor - Advanced Editor?)
- PowerBI DAX Compare the difference between values based on ID and Date using slicers