Have a row displayed with 0/NULL when filtering by date when there's no data

aduell

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?

Thanks!

GrantSmith

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.

1. Take your dataset, get a list of unique dates (select columns, remove duplicates). Do the same for campaigns.
2. For each of those add a constant called 'Join Column' and set it's value to 1.
3. Join both of these together using the Join Column as your join key. This will do a cartesian join which will
4. Note: Since this may drastically increase your rows in your dataset since you now have every campaign for every date.
5. Take the output of this join and left join your original dataset to this based on the date and campaign name.
6. 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.
7. Drop your Join Column from your dataset.

JasonKerr

This would be a great feature to fix. Forcing an ETL for something as simple as populating NULL dates with 0 is very inefficient and frustrating to users.