Have a row displayed with 0/NULL when filtering by date when there's no data
Tried my best to summarize in the question...
I have a data set of Google Adwords Data.
I'm putting the data into a Pivot table and have campaigns as a Row.
I'd like to have every campaign show in the table even when it has no data to report for the selected time frame. It essentially would the the row for the campaign and 0's for all of the value columns.
What's the best way to achieve this?
Domo only displays data that it has in the dataset. If you want to display all possible combinations of campaigns you'd need to have each campaign listed for each day.
**Was this post helpful? Click Agree or Like below**
- Take your dataset, get a list of unique dates (select columns, remove duplicates). Do the same for campaigns.
- For each of those add a constant called 'Join Column' and set it's value to 1.
- Join both of these together using the Join Column as your join key. This will do a cartesian join which will
- Note: Since this may drastically increase your rows in your dataset since you now have every campaign for every date.
- Take the output of this join and left join your original dataset to this based on the date and campaign name.
- Use a formula tile with COALESCE(`Metric Field`, 0) for each of your metric fields to default them to 0 if there is no data.
- Drop your Join Column from your dataset.
**Did this solve your problem? Accept it as a solution!**0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 195 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 81 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 175 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive