9 Replies Latest reply: Aug 28, 2014 11:00 AM by ARICE01 _ RSS

    Numeric Null

    yesb _

      Hi,

         I've a calculated field of type numeric.

         I m using a formula on field-1 of char type

          

         If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

      Like this

      If([isnull[field-1], NULL[/b] ,val())

      (calculated field must be numeric type)

       

      Thanks

        • Numeric Null
          Grant Perkins

          From

           

          Options Menu  >> View

           

          what settings do you have?

           

          Grant

           

           

          Originally posted by yesb:

          Hi,

             I've a calculated field of type numeric.

             I m using a formula on field-1 of char type

              

             If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

          Like this

          If([isnull[field-1], NULL[/b] ,val())

          (calculated field must be numeric type)

           

          Thanks /b[/quote]

          • Numeric Null
            Gareth Horton

            Hi

             

            It depends on what you want.  If you want the absolute text NULL to display, then you will have to create a character calculated field with the if condition setting "NULL", if you want a true null in a numeric calculated field, as seems to be the case here, just have the if condition set 1/0 (a divide by zero error).  Then, how the null displays will be dictated by the editable setting that Grant referred to in Options|Display null values as:

             

            Gareth

             

            Originally posted by yesb:

            Hi,

               I've a calculated field of type numeric.

               I m using a formula on field-1 of char type

                

               If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

            Like this

            If([isnull[field-1], NULL[/b] ,val())

            (calculated field must be numeric type)

             

            Thanks /b[/quote]

            • Numeric Null
              yesb _

              Thanks  Gareth,

              1/0  Technique[/b] is worked fine.

                • Re: Numeric Null
                  ARICE01 _

                  All:

                   

                  I am trying to calculate a Total across a number of numeric values, where certain values are Null.  When the calculation encounters a Null value, Modeler refuses to calculate the Total and renders the Total as Null.  I am sure there is a simple workaround. Can any one help me with this one?  I am coming up blank at the moment with a workaround.

                   

                  Thanks in advance

                  Al

                  • Re: Numeric Null
                    RalphB _

                    Hi Al,

                     

                    Did you try what was posted above in Gareth's post if(isnull(field1),0,field1)?  Insert that formula for each field you are totalling up across and that should take care of nulls.

                      • Re: Numeric Null
                        Olly Bond

                        Hello Al,

                         

                        You can clean up nulls in the table using the if(isnull)) trick, but you can find nulls creeping in to summaries where there's no data that matches your criteria, especially when using an across key. Your totals should still work though - are they calculated as a normal sum() measure?

                         

                        Best wishes

                         

                        Olly

                        • Re: Numeric Null
                          ARICE01 _


                          Thanks RalphB... I guess I was being lazy and hoping there was a simpler way.

                           

                          My current formula in Summary is:  Sum(Jan)+Sum(Feb)+Sum(Mar)+Sum(Apr)...etc, and in some cases a Month value is Null.  There are many Rows for each Month.   I was trying to avoid doing the IsNull() test on every Month.  I guess there is no way around this, since my Summary has both Column Totals and Row Totals.  So I guess I have to take the long road...

                           

                          Thanks,

                            • Re: Numeric Null
                              Olly Bond

                              Hello Al,

                               

                              Is your source data a report or a database? Surely it's easier to use multiple column region to have Month as a field in the table.

                               

                              Best wishes,

                               

                              Olly

                               

                              Sent from my BlackBerry 10 smartphone.

                                • Re: Numeric Null
                                  ARICE01 _


                                  Hi Olly:

                                   

                                  The source data is an Excel file with complicated, embedded Hyperion query links.  I did not want to edit the Hyperion queries, as the Excel file is maintained by the corp finance group.  I am looking at my summary again, mysteriously, I believe the Sum() function is working now.  Not sure how the Summary calculation started to work.

                                   

                                  I will confirm on this post later if all is well...

                                   

                                  Thanks Olly and RalphB