Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Balance Amount calculation based on invoice and receipt (Billing and Receipt)

Received Response
165
Views
4
Comments
2773333
2773333 Rank 4 - Community Specialist
edited Aug 13, 2024 6:19PM in Oracle Analytics Cloud and Server

Hi

I have a requirement in one of the financials report. The customer has a report with details of Invoice like invoice number, amount, receipt transaction, receipt amount etc.

They need to calculate the Balance amount when each receipt paid against the invoice.

For example if the Invoice amount equals 80 and the same invoice has 2 receipts xxx = 75 and xx = 5. When the Balance is calculated for the first receipt the balance amount will be 5 and when the second payment is made the balance amount should be 0. Like shown in the screenshot below. I tried implementing some formulas but it doesnt give me the desired answer. For the second receipt my calculation goes as (80-5=75, where the balance should be 0).

Any suggestions or help would be appreciated.

invoice.jpg

Regards

Vyshak

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    There is some clients when I implement this and i pass with the same challenge, you could do it use a:

    Remaing =   "invoice amount - sum( Receipt Amount ) group by invoice document)"

    However it not the best solution, this kind of information has to be calculated in the ETL process.

    Kind Regards,

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    +1 to Cesar ... Balances are inventory-style facts and need to be stored in the appropriate fact structure.  Once you have it right, then you can show increase/decrease in balance over time AND also add value by having an aging balance fact built from the base balance fact.

  • 2773333
    2773333 Rank 4 - Community Specialist

    Hi Cesar

    Thanks for your response. Unfortunately our clients approach is to write view out of tables from the trabsactional data and pull the view in BI to create reports. Now I do not have any option in implementing this in the ETL. Do you think I can write this in the view ? Or from the reporting end ?

    Regards

    Vyshak

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    OK, if you dont have any other solutions, you should use analytics function LAG/LEAD to calculate your report.

    Oracle BI EE 11g - Calling Database Analytic Functions - EVALUATE - Rittman Mead Consulting

    Kind Regards,