6 Replies Latest reply: May 15, 2014 10:14 AM by Olly Bond RSS

    Need help with a nested calc field

    JLain _

      I have an AR aging with entries with either negative or positive amounts open but are not associated with an invoice no. Of 20,000 lines of data these entries only represent 300 lines but do carry weight in terms of dollar value.

       

      My problem...I now have two invoice amount columns...the Inv Amount from the source db and an open amount column built through a summary export and subsequent external look up. The problem is the summary export brings in the entire amount of the 300 or so items as one amount and places that amount in each open amount field and aging it appropriately. To fix that I manually delete the blank item amount on the external lookup table before importing...now I have a null for an open amount.

       

      The aging formula currently being used is as follows:

      If(<=30,[Open Amount],0). I've tried the and and or functions to build in if open amount = null then bring in Inv amount but each attempt fails the operand rules.

       

      Tks

        • Need help with a nested calc field
          JLain _

          This formula seems to work in excel but Monarch seems to not want to recognize it

           

           

          If(="null",[Inv# Amount],If(<=30,[Open Amount],0))

            • Need help with a nested calc field
              Olly Bond

              Hello JLain,

               

              A numeric field that shows as "(Null)" doesn't have the string value of null - it has the same logical value as 1/0. You can test for this using the IsNull() function.

               

              if(isnull()=1;[Inv# Amount];if(<=30;[Open amount];0))

               

              (Semicolons are better than commas for expressions, for European users where the decimal character is a comma. They also work fine for US & UK users.)

               

              Best wishes,

               

              Olly

                • Need help with a nested calc field
                  JLain _

                  Olly,

                  I see how the formula did convert the null value to the Inv amount...however, now the false value of the aging formula is being ignored because each of these null values are all aging in this current bucket. Will it work if the "is null" function works in the false part of the if then statement?

                    • Need help with a nested calc field
                      JLain _

                      I think I might have figured it out with a modification to the formula you provided.

                       

                      if(isnull()=1 .And. <=30;[Inv# Amount];if(<=30;[Open amount];0))

                        • Need help with a nested calc field
                          JLain _

                          Olly, need help por favor. So, my aging report has changed due to ERP's. I am building an agin bucket to exclude zeros...however I dont seem to be able to get it done. Could you please take a look..this is the formula i'm using

                           

                          if(isnull()=1 .And. <=30;[Open Amount];if(<=30;[Open amount];0))

                            • Need help with a nested calc field
                              Olly Bond

                              Hello Joe,

                               

                              Your formula looks like it should return null, or nonsense, values, because it's checking for nulls:

                               

                              if(isnull()=1 .And. <=30;[Open Amount];if(<=30;[Open amount];0))[/CODE]

                               

                              This is saying: if "Open Amount" is a null value (and is less than 30 days old), then show the Open Amount, if not show zero.

                               

                              In your previous post, the logic returned the Invoice Amount if the Open Amount was null, like this:

                               

                              if(isnull()=1 .And. <=30;[Inv# Amount];if(<=30;[Open amount];0))[/CODE]

                               

                              Do you still have the Inv# Amount field?

                               

                              Best wishes,

                               

                              Olly