Including 0 values in Insights reports

Have more questions? Submit a request

20 Comments

  • Mandeep Singh
    Comment actions Permalink

    Awesome! This is really helpful :) Thank you Sarah!

    0
  • Satia Stevens
    Comment actions Permalink

    Whew, thank you for this article!

    0
  • Jessie Schutz
    Comment actions Permalink

    We're glad it helped, Satia! :)

    0
  • Matt Savage
    Comment actions Permalink

    This is very handy!  Is there any similar workaround for a report with multiple attributes?

    0
  • Matt Hoffman
    Comment actions Permalink

    @Matt - The short answer: maybe. The long answer - probably not, because of the way that GoodData handles null values on metrics and attributes. However, even if this exact solution can't be applied to your problem, there could be another trick that could be applied. Since it's totally dependent on your individual use case, I'm going to reach out in a ticket so we can take a look at the specifics.

    0
  • Mike Althoff
    Comment actions Permalink

    This solution is working for zeros inside the x-axis range, but I'm still getting blanks if the ends have zeros.  i.e. in your example if 12AM or 11PM were 0, they aren't showing up on the graph.

    Is there anyway to force display those 0s?

    0
  • Sarah Hale
    Comment actions Permalink

    @Mike  I'm going to open a ticket for you so we can look at your specific report.

    0
  • Oliver Knigge
    Comment actions Permalink

    Thanks for the hint!

    0
  • Jennifer Holmes
    Comment actions Permalink

    Hi We have the opposite situation; is there a way on a table style report to exclude 0 values from displaying and just show a blank space. We have a table with 8 custom ticket metrics across the top and it is proving difficult to read because so many have 0 values. Would be great to exclude from displaying as you can in excel

     

     

    0
  • John Tolle
    Comment actions Permalink

    Thank you for the tip!  Totally works for me, although I admit that I was confused because I thought the number format of X0 would ensure that zeros always showed up.  I suppose because it's actually null, it's not formatted at all, hence nothing shows up.

    Which confuses me since @Jennifer Holmes is having the exact issue I expected to have.  My only thought is that she is actually getting zeros rather than nulls.

    Jennifer, I haven't tested this yet, but would something like this work?

    SELECT IF (YOUR_SELECT_HERE) = 0 THEN "" ELSE (YOUR_SELECT_HERE) END

    Granted, that's not terribly efficient, so there's probably a better way it can be written to remove the redundancy, but I'm not experienced enough to know how to do that.

     

    0
  • Dan Kondzela
    Comment actions Permalink

    Hey John, that is a clever workaround I think. Have you since implemented this into any reports to any success? If you have run into issues, what kinds of issues were they?

    I look forward to hearing back!

    0
  • John Tolle
    Comment actions Permalink

    Nope, I haven't tried it myself because I was having the opposite issue: Null values were showing up as nothing and I wanted zeros.  The IFNULL function worked fine for me (and it's better since it only requires your select statement to be run once):

    SELECT IFNULL((SELECT YOUR ORIGINAL STUFF HERE), 0)*1

    You could replace the 0 with empty quotes "" or anything else.

    I'm not actually sure what the *1 is for, and I've omitted it without any negative effects.

    Anyhow, IFNULL obviously doesn't help when you are getting zero and you would prefer nothing at all.

    It'd be nice if there was some kind of IFZERO function that did the same thing, but there is not, as far as I can find.

    The IF THEN ELSE could be the way to go, but even the docs don't make it clear if that would work (and even if it does, there's definitely a performance penalty since it has to run that query twice).

     

    0
  • John Tolle
    Comment actions Permalink

    And I just now found an even better way to handle nulls instead of using IFNULL.  This could also, in theory, handle zeros (by replacing them with nothing):

    Metric Format (a.k.a. Number Formatting)

    Something like this ensures that nulls get turned into zeros:

    #,##0;
    [=null]0

    Maybe this would work for your situation (turning zeros into nulls):

    #,##0;
    [=0]

    or maybe, if "nothing" isn't an option, just a period(.) is good enough:

    #,##0;
    [=0].

    0
  • Jennifer Holmes
    Comment actions Permalink

    @John

    the second option adding #,##0;[=0] to the number format worked perfectly.

    My colleagues will be pleased, thanks for the tip.

     

    0
  • John Tolle
    Comment actions Permalink

    You're welcome, @Jennifer!  Sometimes it pays to just keep throwing darts... eventually one finally makes it to the dart board.  :)

    0
  • Jennifer Holmes
    Comment actions Permalink

    @ John I agree, I guess a lot of it comes down to having the time and patience to experiment.

    Since I'm here, the other thing that is driving us nuts at the moment is there not appearing to be a way of automatically align the numbers to the left of the field i.e.  under the column header. So far the only way we can figure to achieve this is to manually manipulate the column width but that seems very clunky.

    0
  • John Tolle
    Comment actions Permalink

    Short version: Just stick with manually changing the column width.

    Numbers are always right-justified (which is pretty standard) and I don't see any way to choose any other kind of justification or other positioning options.  I couldn't even figure out a cheat, such as adding spaces into the number formatting (they're ignored).  The best you could do is terrible: Add another character like underscores to push the number to the left, like this:

    #,##0_____;

    But that's just ugly.  HTML has a non-breaking space ( ) but that is treated as literal text and even pasting in non-breaking spaces from other websites, etc. doesn't work.

    The only real option you have is some kind of complex string manipulation in the custom MAQL SELECT statements, but I'm not sure of how to do that.

    0
  • Jennifer Holmes
    Comment actions Permalink

    @John Thanks for the advise I'll carry on just manually manipulating the fields. i just assumed I might be missing something simple.

    0
  • Josh Keller
    Comment actions Permalink

    Hey folks! It looks like this issue of showing 0-values on the x-axis when there are multiple metrics may have been sorted out privately. I'm sure hoping that's the case, and that someone can share the trick with us.

    I'd like to get this report to show the 0 values for November and January.

    All 3 of these metrics are like such:

    I've tried the following variations for the format as suggested above:

    #,##0.00%;[=0]
    #,##0.00%;[=null]0

    Any ideas here? This would really be a bummer if it's not possible.

    0
  • Nicole - Community Manager
    Comment actions Permalink

    Hey Josh -

    Sorry that no one has been able to help you out. If you'd like, you could try re-posting your question in the Insights and Reporting Q&A topic in the community to give it more visibility and see if someone over there has an answer for you.

    0

Please sign in to leave a comment.

Powered by Zendesk