Transforming Date Range to Zero

tobypenn

I have a simple dataset that has activities over time. I like to break that down by week so I can see the week to week workload of the team. For some of the activities being tracked in the data there might not be anything so in my line chart it shows as a blank and the line graph breaks. I've looked at "hide empty values" but that connects one point to the next rather than dropping it back down to zero. Is there a beast mode function I can write that would say for for this week, if null (no values time period) then 0 else count however many there actually were? You can see no activity between week 4 and 6 for the red series. no activity between week 7 and 9 on the green series. etc... how to I drop those to zero?

GrantSmith

You'll need to utilize a calendar dimension dataset. Domo has one in the Domo Dimensions connector (calendar.csv). This has all the dates from 2010-2030. I'd recommend filtering the dataset to be before the current date and after or equal to the start date of your data. This will reduce the extra rows which won't be helpful to you. Start with this in your ETL and then LEFT join your data to it. This will cause all of the dates to be populated, however because you're doing a left join the values will be null. You can use a formula tile and then alter your count field with COALESCE:

COALESCE(`Amount`, 0)

This will default the values to 0 to force them to show up on your card.