Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Can I replace 0 with a hyphen -

Received Response
311
Views
8
Comments
Youssef_B_82
Youssef_B_82 Rank 5 - Community Champion
edited Aug 13, 2024 5:58PM in Oracle Analytics Cloud and Server

Hi,

Is it possible to replace a value by a hyphen "-" when it's equal to 0?

I want to display a hyphen instead of 0 on an analyses, so for example when sales = 0 I want to see " - "

Thanks

Youssef

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Sure you can ....

    It will require a CASE WHEN in the formula of the column, that's how you can replace the 0.

    So something like CASE WHEN <your thing> = 0 THEN '-' ELSE <your thing> END

    Assuming you really talk about existing 0. If it's what you display when there is no value it is set in the format of the column (you had to use a custom format).

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Except for the obvious problem of this being a question of "is it actually correct to do this"?.

    You may implicitly think that it's "logical" to you but it's a question of pre-aggregate vs post-aggregate as well: Do you mean row-by-row evaluation of that logic in the data source? If so, then it can make sense. If you are talking about anything else in terms of post-calculation either in the RPD or in the front-end anything you see as a "row" is a totally virtual construct and an assemblage / aggreation already.

    Sales for one single, atomic row 0 the DB = 0 (the number zero) then render as ' - ' (blank string, hyphen, blank string) can be ok.

    But "Sales" as a measures dynamcially reacting to the surrounding dimensionalities etc? Less so. "Sales" on country level can be 0 because you had 10 rows with positive 100 and one row with -1000 (negative 1000) means an aggregated "Sales" of 0. Would that be correct to render with your string or be logically unsound?

  • Youssef_B_82
    Youssef_B_82 Rank 5 - Community Champion

    Hi Gianni Ceresa,

    My issue is resolved.

    I tried the CASE WHEN, but it didn't work, I got an error message

    pastedImage_2.png

    I got it to work by adding a conditional format:

    pastedImage_7.png

    By doing so I got the desired output.

    pastedImage_8.png

    Thanks for your response

    Youssef

  • Youssef_B_82
    Youssef_B_82 Rank 5 - Community Champion

    Hello Christian Berg,

    I see what you mean. Our client request to see '-' instead of zero. We figured it out how to do so by adding a conditional format. Issue resolved,

    Thanks for your help

    Youssef

  • Daniel Willis
    Daniel Willis Rank 4 - Community Specialist

    Wonder how the CASE statement would compare to if (assuming column is X) you changed formula to X/X and then used the number format #,#.00;-#,#.00;-

    So X/X would return null for x=0 and then be formatted as -

    </random thought>

  • Gianni Ceresa
    Gianni Ceresa Mod

    I would definitely prefer the format mask approach, but it requires to first transform the 0 into NULL.

    Actually a CASE WHEN to make the 0 a NULL and then using the format mask would be the cleaner approach I guess ... (mainly because the column stays a number). I just generally do not like these kind of things as 0 and NULL are totally different things (even if many still don't get that 0 <> NULL <> '' )

    Daniel: You mean to use the X/X as an hidden column you just use as condition? (I don't see how it can be the real column itself as it will change the values)

  • Daniel Willis
    Daniel Willis Rank 4 - Community Specialist

    oops yeah the X/X wouldn't work.. didn't really think enough about that. How about X/X*X. Was just thinking of an alternative to a CASE. If X=0 then X/X*X should return null and otherwise X? No idea whats faster

  • Gianni Ceresa
    Gianni Ceresa Mod

    anything with a / will generate an error "division by zero".

    In general people ask how to make NULL a 0. In this case the "less worse" crossing my mind is really a CASE WHEN "...." = 0 THEN NULL ELSE "...." END.