Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Create a new column based on distinct result

Hi,   Good day.   I need some help to get an accurate audit result from a given audited data. But first, let me share to your our audit process and give you samples. See details as follows:   1...
General Comment
Need Help
Show and Tell
Tips and Tricks
Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

I don't know how your last table is calculated. It shows 1200 for first Audit, but in your table you have 4 lines with 1200, 1200, 1000 and 1200. Is this a MAX-calculation?

 

BR

 

Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Jimmy801 ,

     

    Good day.

     

    I cant say if this is a MAX-Calculation since I dont have any idea. But the amounts indicated there are distinct and might be based on the document submitted. For example:

     

    1200 is the total amount of all the BULK receipts. The reason to have 1000 amount in question, might pertain to a one receipt with an amount of 1000 and we need to raise an issue. That is why we showed it in a diffrent row. On the other hand, once we raised, 1200 meaning all of the receipts have same issue only and applied to all. 

     

    I hope I answer your question 🙂

     

    Thanks and regards

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      Hello Anonymous 

       

      sorry, I don't get what you want to have calculated in your custom column.

      Your data model shows this with values of 1200, 1200 1000 an d1200 in the colum AMOUNT IN QUESTION

       

      Your grouped table shows this

       

      I have to know how this column is grouped/created.

       

      BR

       

      Jimmy

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Jimmy801 ,

         

        Good day.

         

        Apology on that. The table was created by using this formula in excel:

         

        "Per Original Unique Reference, if Spend Expense Amount (USD) based on Original (Y) is greater than Sum of Amount in Question, then value is equal the Spend Expense Amount. Otherwise, Sum of Amount in Question."

         

        To illustrate, for example:

         

        ORIGINAL UNIQUE REFERENCE# (sequence per line item, i.e. 1,2,3) of Inserted Line NEW UNIQUE REFERENCEORIGINAL (Y)Customer Name Spend - Expense Amount (USD)  AMOUNT IN QUESTION SPECIFIC
        AUDIT ISSUE        		Current Audit Status
        OrigUniqRef_01 OrigUniqRef_01YCustomer A               1,200.00                1,200.00Expense for ClarificationWith Issue
        OrigUniqRef_011OrigUniqRef_01.L1 Customer A               1,200.00                1,200.00Business Purpose Not ProvidedWith Issue
        OrigUniqRef_012OrigUniqRef_01.L2 Customer A               1,200.00                1,000.00Only Credit Card Slip/Statement is provided.With Issue
        OrigUniqRef_013OrigUniqRef_01.L3 Customer A               1,200.00                1,200.00No Time ChargesWith Issue

         

        The Spend Amount in (Y) is = 1200

        Sum Amount in Question = 4600

        Then, for this Uniq Ref#, the audit result amount will show = 1200. (Because the original amount for this particular Row is 1200 only).

         

        However, if there are errors in our formula, we check the audited file and edit it manually. For another example,

         

        ORIGINAL UNIQUE REFERENCE# (sequence per line item, i.e. 1,2,3) of Inserted Line NEW UNIQUE REFERENCEORIGINAL (Y)Customer Name Spend - Expense Amount (USD)  AMOUNT IN QUESTION SPECIFIC
        AUDIT ISSUE        		Current Audit Status
        OrigUniqRef_02 OrigUniqRef_02YCustomer B               1,500.00                    900.00Receipt not providedWith Issue
        OrigUniqRef_021OrigUniqRef_02.L1 Customer B               1,500.00                    900.00Expense for ClarificationWith Issue

         

        The Spend Amount in (Y) is = 1500

        Sum Amount in Question = 1800

        Technically, the amount must appear only is 900 because the only amount with raised issue is the receipt with 900 in amount. However, it appears wrong if we use the formula we created for this Audit Result column that is why other rows with the same kind of scenario, we edit it and correct it manually.

         

        I hope I got this one as answer to your question. 🙂

         

        Thanks and regards,

        Echo