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

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

Comments

  • swyatt
    swyatt Contributor

    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):

    nuts.png

     

    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. 

  • 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
    swyatt Contributor

    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. 

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

    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?
    daterange.png

     

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

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

    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

    -----
    I work for Domo.
    **Say "Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as "Accepted Solution"
  • 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

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

     

    Thanks much!

  • 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

  • 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

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

     

    Regards,

    Dani

  • Archer
    Archer Domo Employee

    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
    Shevy Contributor
    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.
    Dojo Community Member
    ** Please like responses by clicking on the thumbs up
    ** Please Accept / check the answer that solved your problem / answered your question.
  • 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
    Shevy Contributor

    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)

    Dojo Community Member
    ** Please like responses by clicking on the thumbs up
    ** Please Accept / check the answer that solved your problem / answered your question.
  • 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?