5 Replies Latest reply: May 15, 2014 9:56 AM by Kathi _ RSS

    Problem with Calculated field

    Kathi _

      I imported a field from an external source (Excel).  I then used that field in a calculated field.  If the imported field has a value (i.e., not zero) then the calculated field is perfect.  However, if the imported field was zero, then the calculated field is also zero.  Basically, I am taking charges less credits less payments to get a net amount.  The payments field is imported from Excel and the net amount is calculated.  If there is a payment, then the net amount is calculating perfectly.  If there is no payment, then the net amount should equal the charges less the credits.  However, it is showing zero.  Could the problem be in the import?  I'm thinking that maybe the import isn't finding a match and getting nulls and therefore the formula is bombing?  I know that in my summaries I have null fields showing zeros...  :confused: 


      Also, if I have multiple lines in my original table and I have a single line in my import for that person, it is importing onto each line.  Is there a way to stop that?

        • Problem with Calculated field
          Data Kruncher

          Hi Kathi, welcome to the forum!


          You've hit it on the head: you're seeing zeros in the imported values because the lookup value couldn't be found (no match), and the null option is set to display zeros in your model. Go to Options, View... to see the literal which will be displayed for null values.


          When your calculated net amount field tries to do a calculation on a null value, you're right again, it bombs. In fact, the calculation returns a null value, but you can't see that because the model is converting the null to a zero. This is one reason I like to display something like "*NULL*" instead of zero or blanks.


          The solution is to take this into account, and use Monarch's IsNull function in your calculated field, similar to this example formula for a calculated Net field:


          [font="courier"]If(IsNull(Payment),Charges,Charges-Payment)[/font][/quote]The Net amount will be equal to Charges if the Payment is null (not found), and the net amount will be Charges less Payment when a payment is found.


          As to how to have a single payment amount imported when you have multiple charge lines for a specific individual, I cannot envision a solution for that now. I'll post back if I come up with something, but do invite other forum members to chime in.



          • Problem with Calculated field
            Kathi _

            That did fix the calculated field problem!  Thanks, Kruncher!


            If you (or anyone else) can come up with a solution to my single vs multi line problem, I would be grateful!

            • Problem with Calculated field
              Grant Perkins

              Originally posted by Kathi:

              Also, if I have multiple lines in my original table and I have a single line in my import for that person, it is importing onto each line.  Is there a way to stop that? /b[/quote]Kathi, it sounds like you either need to summarise the multiple lines into a single line per person (if that is all you have available on the imported side) or look for an extended key for the look up - multiple fields perhaps such as person and transaction reference or account number for example - so that the imported value only matches one of the multiple lines.


              The question that comes to my mind is whether the matches you are currently getting are accurate. If you get multiple lines imported that match multple lines for a person in your original table, how do they match? Luck perhaps? Or is there something else which makes the lookup work correctly? If so whatever that is needs to be applied to the single imported lines as well.


              I may be wide of the mark on this one but I have found myself in similar situations before now so thought it best to raise a warning flag!






              • Problem with Calculated field
                Data Kruncher



                I think I've got a solution for you, based on Grant's comment on summarized records.


                Since you didn't specify what your lookup field is, for arguement's sake let's say it's customer number.


                Let's say you've got 3 charges on a particular account and a payment to import. With the current situation, the payment amount you'd end up would be three times the actual payment amount, right?


                To get the proper Net amount, I propose you break this up into two distinct activities. First, create a model which gives you a summary in which you get total charges on one line per customer number. Export that summary to an Excel file. Now save this model, and close the report and the model.


                The second step is to read in that new Excel file as a database source. To that data, you'll now import the payment data from the other file. This will give you the accurate Net amount. Obviously, save this model too.




                • Problem with Calculated field
                  Kathi _

                  Thanks for the ideas.  I'll give them a shot.  I've also looked at modifying the query that is creating the excel spreadsheet that I am importing the payment from.  If I can get the appropriately modified, I may have another key to match the import on that will cut back on the problem.


                  I appreciate all the help!  I've been reading the forum for ideas long before I joined.  I knew you guys would be able to help!