3 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Values 0.00

    jdino73 _

      My project is setup for all null values to show 0.00.  If a calculated field is pullin gthis column as data , it shows zero as my result, it seems to cancel out my formula.

       

      Any suggestions

        • Values 0.00
          Olly Bond

          Hello jdino,

           

          A calculated field based on a null value will usually return a null value. In monarch, Options > View, show nulls as "0.00" or whatever, is purely cosmetic.

           

          If you are opening data from a database, you can replace nulls with default values for the relevant field type ("", 0, 1/1/1970).

           

          When you are taking data from a report, you'll need to use IsNull() in calculated fields to overcome null values, e.g.:

           

          If(IsNull(OldField)=1;0;OldField)

           

          Best wishes,

           

          Olly

          • Values 0.00
            jdino73 _

            I am using excel as my  external database. Some of the columns that i am bringing in do not have data in them yet. However i would  still like to bring in these columns so i can set up my model and project for future use. When I create a calculated field to add together the 12 columns the result is zero. I belive it has somehting to do with that some of the columns do not have data yet. How can i fix this. I would like to set up the formuala now to add all of the columns together

             

            The forumala i am using in the calcuated field is just a simple

            column a + column b+ columnc+  etc

             

            Your help is greatly appreciated

              • Values 0.00
                Olly Bond

                Hello jdino,

                 

                The easy way to fix this is to create a calculated field for each of the columns you are importing. For each one, the formula should be:

                 

                if(isnull(ColumnFromExcel)=1;0;ColumnFromExcel)

                 

                This will replace the null values with zeroes.

                 

                Once you've done that, you can hide the original columns from Excel and just show the new calculated fields.

                 

                Then to sum them, create a new calculated field as you have already done that adds up the values in the 12 new columns.

                 

                You might also consider using a summary to sum your data?

                 

                Best wishes,

                 

                Olly