7 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    How to handle negative credit values

    fermat _

      I am working on a GL report and I cannot seem to pull the values for lines where there is a negative credit.

       

      For example:

       

         GL Num         Journal Amt

      1  40015-0564   4,648.85

      2  40015-0564   1,648.91 CR

      3  40015-0564  (2,500.00) CR

       

      Grand Total       5,499.94

       

      In the table, line one is correct at 4,648.85

      Line two is correct at -1,648,91; however, line three comes in as (Null)

       

      How do I pick up line three as 2,500.00?

       

      Thanks in advance!

      Fermat

        • How to handle negative credit values
          Data Kruncher

          You'll need to extract the CR designation as its own field, maybe named DRCR.

           

          I'd start by capturing the amount as PreAmount with a Numeric field type. I use the Pre prefix to indicate that the field will be used in a calculate field. Speaking of which...

           

          In the Table window, build a new Amount calculated field with this formula:

           

          if(DRCR="CR",-PreAmount,PreAmount)[/CODE]

           

          Assuming that DRCR can only be DR, CR, or blank, then that should handle it for you.

          • How to handle negative credit values
            Grant Perkins

            Hi fermat,

             

            I think the problem is that Monarch is see a double negative situation and therefore gives a null.

             

            Can you have a negative CRedit?

             

            Both the brackets and the CR are interpreted as negative by Monarch. You need to dump one of them or check why the file has this double sign effect

             

            Once you know what you want to do in this situation we can advise which way to approach it.

             

            HTH.

             

             

            Grant

              • How to handle negative credit values
                Olly Bond

                Hello Grant,

                 

                In this case, I fear that losing the () or the CR would result in nonsense data, as a negative value might be treated as positive. If we can't persuade Monarch to pick up on two indicators of negative to make a positive, then I fear a calculated field will be the only way.

                 

                Best wishes,

                 

                Olly

                  • How to handle negative credit values
                    fermat _

                    I went with Data Krunchers suggestion and that worked very well.  I hid the original JE column and the CR flag so the report look exactly the same.

                     

                    Thanks for the advice all!

                     

                    s/ F

                    • How to handle negative credit values
                      Data Kruncher

                      While unusual, situations can and do arise where an account is mean to have a credit balance, but in fact is negative, so in effect has a debit balance.

                       

                      Usually the CR label is forced onto the report to indicate the "normal" state of the account. In those cases a positive value would indicate a value that really is negative, and a negative number would in normal circumstances be a positive number.

                       

                      For instance, sales accounts are normally credit balances, so such a report would show 1,000 as $1,000 in sales, while (1,000) would be negative sales, maybe indicating more returns than sales, if no separate account for returns exists.

                       

                      More often than not, double negatives are the results of accruals or other journal entries.

                       

                      Way off topic now. Enough of my ramblings...

                        • How to handle negative credit values
                          Data Kruncher

                          Thanks for the update fermat, while I was rambling.

                            • How to handle negative credit values
                              Grant Perkins

                              Kruncher's post was not around when I started to prep mine or I would have left it.

                               

                              As Olly pointed out and DK confirmed, the issue is more one of understanding how the particular system/report is presenting the information, understanding why it is going it like that and then processing accordingly. In this case apparent conflict of sign, as Monarch would interpret things in a catch-all field is what needs to be inderstood and processed for. Appropriate processing of the value field and the reported indicator separately is the best way forward being unambiguous in its result - hopefully.;)

                               

                               

                              Grant