Including 0 values in Insights reports Follow

Comments

18 comments

  • Avatar
    Mandeep Singh

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

  • Avatar
    Satia Stevens

    Whew, thank you for this article!

  • Avatar
    Jessie - Community Manager

    We're glad it helped, Satia! :)

  • Avatar
    Matt Savage

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

  • Avatar
    Matt Hoffman

    @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.

  • Avatar
    Mike Althoff

    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?

  • Avatar
    Sarah Hale

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

  • Avatar
    Oliver Knigge (Edited )

    Thanks for the hint!

  • Avatar
    Jennifer Holmes

    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

     

     

  • Avatar
    John Tolle

    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.

     

  • Avatar
    Dan Kondzela

    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!

  • Avatar
    John Tolle (Edited )

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

     

  • Avatar
    John Tolle

    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].

  • Avatar
    Jennifer Holmes

    @John

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

    My colleagues will be pleased, thanks for the tip.

     

  • Avatar
    John Tolle

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

  • Avatar
    Jennifer Holmes

    @ 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.

  • Avatar
    John Tolle

    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.

  • Avatar
    Jennifer Holmes

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

Please sign in to leave a comment.

Powered by Zendesk