6 Replies Latest reply: May 15, 2014 9:58 AM by Jessica Daw RSS

    Extracting an Amount

    Jessica Daw

      Hello,

       

      On a report I am creating: in the table view, when a number in the 'coverage code' column is a set number, I am trying to take an amount out of the 'total' column and enter it into a new column.  I cannot seem to get the statement correct, and am not sure even if an If statement is correct to use for this.

       

      When I try the following (and similar entries):

      If(='21',[Total Charge Amount],"")

      I receive an operand types error

       

      Can you point me in the right direction?

       

      Thank you,

      Jessica Daw

        • Extracting an Amount
          Nick Osdale-Popa

          OK, you're close.

          /size[list=1][]Is your calculated field numeric?  It should be as you are trying to return an amount which should be numeric.[/size][]In your calculated field you are returning a number if your statement is true, or a string ("") if it is false, this is your operand error.[/size][/list]

          Your calculation should read as such:

           

          If(='21',[Total Charge Amount],0)[/b]

           

          (this will return 0 (zero) if it doesn't match)

           

          If you want it to actually be blank use:

           

          If(='21',[Total Charge Amount],1/0)[/b]

           

          1/0 will return a null value in Monarch...very helpful to use when you actually want a numeric number to be blank vice 0 (zero).

           

           

          Hope this helps!   

           

          [size="1"][ June 24, 2003, 07:05 PM: Message edited by: Nick Osdale-Popa ][/size]

          • Extracting an Amount
            Tom Whiteside

            Hi, Jessica!

             

            Be sure and remove the single quote marks from around the number 21.  If it truly is numeric, the formula will treat it as a character if the marks are left there.

             

            Nick's suggestions are usually flawless      , but this is one time he let his guard down and his computer got him with a good return kick      !

            • Extracting an Amount
              Nick Osdale-Popa

              Originally posted by Tom Whiteside:

              Hi, Jessica!

               

              Be sure and remove the single quote marks from around the number 21.  If it truly is numeric, the formula will treat it as a character if the marks are left there.

               

              Nick's suggestions are usually flawless        , but this is one time he let his guard down and his computer got him with a good return kick        ! [/b][/quote]Hey! I resemble that remark!   

               

              Now, there is an assumption being done here as to whether is numeric or alpha-numeric. Yes, if it's numeric, the '' need to be removed, but the fact did remain that the formula was returning two different data types, a numeric value () if true or a blank string ("") if false.  This would be the most obvious cause for the operand types[/i] without knowing the how the fields are defined.

              • Extracting an Amount
                Jessica Daw

                Thank you, Nick and Tom!  The column numeric, so taking the ' is what needed to be done.  Also, the 1/0 to return a blank value worked when I exported to Excel, which is exactly what I needed, but when I exported to a text file the (null) appeared.  Is there anyway to actually return a blank value, or is (null) just the same in Monarch?

                • Extracting an Amount
                  Tom Whiteside

                  Jessica, forgive me if you have already checked this, but on your main menu go to: Options - View - Display Null Values As: and see what is in the box.  The default value for versions 5 and 6 used to be (null).  If you clear this box, your nulls should display as blanks.

                   

                  I usually export to MS Access files and Access hates blank values in the middle of numeric ones.  To overcome this, I use calculated fields to change the nulls to zero values, something like:

                   

                  If(IsNull(), 0.00, )

                   

                  (I learned early that setting Display Null Values As: 0.00, simply puts "0.00" in as text   :rolleyes:  .  Oh well!)

                   

                  Hope this solves your display problem.  Good luck!

                  • Extracting an Amount
                    Jessica Daw

                    It said (Null).  Thanks!  [img]smile.gif[/img]