Map a zero value for months that don't have data

bensontruven

How do I map a zero (0) value for a month in a dataflow so the month name will show in a card even if there is no data to display?

 

Thanks,

 

Steve

swyatt

Hello Steve,

 

This may be a ridiculous question, but are you already experiencing issues? The month values should display unless this box is checked under chart properties (category scale):

 

Also, when you create cards are your months showing in chronological order? I want to see if it's accepting your "Date" field as an actual date and not a string. 

bensontruven

So yes...I have a card that looks at the different types of sales (New, Renewal, etc). On one of the cards I created for a single region, I was only looking at New sales and that region didn't have any New sales in May and the month name doesn't appear. I looked on the card and that box is not checked.

swyatt

That is strange. For the different type of sales (New, Renewal, etc.), are they defined by a formula or is there a column something to the effect of `Sales Type`? If it's a formula, I'm wondering if there is a discrepancy in the time reference. 

bensontruven

It is a field that comes directly from SFDC with the name Type...so no formula to have any discrepancies in.

swyatt

Okay, I'm not really familiar with the Salesforce Data Connector so I'll looping @letsdothis from DOMO. A couple of other ideas though:

 

Is your date range field selected at the bottom of the "Edit Card" page?

 

If so, what does your date range show at the top, right above the summary number?

bensontruven

My Date Range field is CloseDate and at the top the date range is Year to Date, by Month.

letsdothis

Hello bensontruven,

I'd like to get a little more context surrounding this issue. Can you submit a support ticket to support@domo.com with a link in the ticket to the affected cards and datasources? After we solve the issue we can have the support engineer come back here with the solution for the community.

 

-letsdothis

bensontruven

Hello letsdothis:  Here is the case number: 00751535

 

So the card is pulling in Closed Won data for an opportunity type that is New or Cross-Sell.  In the month of May, there were no opportunities with the opportunity type of New or Cross-Sell that were Closed Won.  I want the month name to still show up even though there is no data...this will call out that nothing was sold.  

 

So how can I get the Zero Value month to show up in the chart?

 

Thanks,

 

Steve

northshorehiker

Did you ever manage to get a solution for this? I'm currently dealing with a very similar issue.

 

Thanks much!

[Deleted User]

I looked in the case notes and see this recommendation:

 

"Advised data set would have to include one row of data from any missing months, even if all other columns were blank."

 

Please give it a try.

 

Regards,

Dani

northshorehiker

Thank you for the reply...

 

My datasource is used to feed a number of cards and contains detail data rows, including a date/time field... I'm using filters to display a vertical bar graph of the count of the number of rows where a status-type field contains a particular value (y axis) for each month in the range.  (x axis). Therefore, if I add a row to my datasource for each month that's missing from this chart, that's going to be erroneously counted as an occurrance of that value.

 

Short of creating a separate datasource that pre-aggregates these rows by month, am I out of luck?

 

Thanks again for your assistance.

 

==

Chris

[Deleted User]

OK I will have someone from the support team take a closer look at this for you.

 

Regards,

Dani

Archer

The solution is to use a dataflow to join your dataset with calendar data that includes the missing dates, weeks, months or years. Then the missing date will be represented in the dataset even if there is no associated data such as sales numbers. This will allow missing date information to be represented in Domo cards.

 

Domo Support can provide access to an extended calendar through a Domo Dimensions connector. Simply request this calendar through a Domo support ticket created through Domo Buzz, email or by phone call.

Shevy

I have solved this by creating a date table that I link most "actual" results to. DOMO will not display results for months that have no volume and this can be good or bad depending on what you are trying to do. By creating a join on a date column - and 'left join" based on date month - you can ensure all months are shown. This will also allow you to show future months as blanks if needed for consistency and charts (management often likes to see this). Let me know if you need more details, fairly straight forward.

CBlandon

I'm running into this same problem. I have joined two datasets. One has leads per day by employee. The other has hours per day by employee. If an employee worked a particular but didn't generate any leads their hours for that day aren't showing up. This impacts a beastmode I created to calculate leads per hour. It's artificially higher because the hours are lower. I'm linking the two datasets on date and employee. 

 

I tried to add another dataset with every date and every employee per the latest solution but I'm still having trouble.

 

Any thoughts?

Shevy

what kind of join are you doing?  Left join, inner join...etc...  Also, you may want to force a 0 (zero) into the volume clumn when you do the join for those where there are hours worked and no volume (null values sometimes gives odd results)

CBlandon

Thanks for the fast reply!

 

I've tried left, right, and outer joins. None get me to where I want to be. How would I force a zero?