Question on how to get a earliest AR transaction date based on outanding balance into a balance rep

Richard Halford

Hello,

I'm working on an analytics using the Financials - AR Transactions subject area

What I am pulling in is:

Attributes:

Customer Name, Customer Number, Customer Account Name, Customer Account Number, Paying Customer Site Name,

Measures:

Facts - Document Currency Invoice Amount, Invoice Count & Remaining Amount

I also want to pull in:

Earliest Transaction Date which I have done via Create Calculation and the expression "MIN("Financials - AR Transactions"."Transaction Date"."Transaction Date")"

The one I am struggling with is Earliest Incomplete Transaction Date

The calculation needs to be something along the lines of

Earliest Transaction Date i..e - Min ("Financials - AR Transactions"."Transaction Date"."Transaction Date") but it needs to exclude any transactions where the Remaining Amount is 0

So for example if customer - A Nonymouse has 3 invoices:

12-Dec-2021 for $50 fully paid

01-Feb-2023 for $30 and $10 remaining

07-March-2024 for $100 and $100 remaining

Then I would get an invoice count of 3, Earliest Transaction Date of 12-Dec-2021 and Earliest Incomplete Transaction Date of 01-Feb-2023

In SQL I'd run a sub select in to retrieve the min where remaining amount > 0, I can't use a Create Calculation and Filter as filter expects a measure rather than date. I guess its a case of how best to do it in FDI rather than some over engineered kludge

JohnW-Oracle

Hi @Richard Halford ,

Thank you for the question. It seems like you are on the right track by using Transaction Date in a calculated column. Transaction Date is 'treated as an attribute' so you should be able to use it in your calculation.

When you add a column like that to a Workbook you can right click > Aggregate to confirm its treated as an attribute.

What is the full SQL statement and what error are you receiving?

Regards,

John

Richard Halford

Hi When I right click I can only do aggregate on count/count distinct on Transaction Date

My thinking was to start with MIN("Financials - AR Transactions"."Transaction Date"."Transaction Date")

but filter the rows returned for the min aggregate by "Financials - AR Transactions"."Facts - Analytics Currency"."Remaining Amount" > 0

which would lead me to min (filter ("Financials - AR Transactions"."Transaction Date"."Transaction Date" USING "Financials - AR Transactions"."Facts - Analytics Currency"."Remaining Amount" > 0))

which you can't do as the syntax is filter(measure USING expression) and can't do filter (attribute USING expression)

for raw SQL, i'd just do a select with an inline select column or in the main from have a sub select there joined to the results

Richard Halford

I;m getting a little further there:

I created another custom calculation

MIN(CASE WHEN "Financials - AR Transactions"."Facts - Analytics Currency"."Remaining Amount" <> 0 THEN "Financials - AR Transactions"."Transaction Creation Date"."Transaction Creation Date" ELSE CAST('2049-12-31' AS Date ) END)

I now get the earliest transaction date where the remaining amount is >0 on all the rows returned (i.e. the min is applying the results to the presentation stage rather than data fetch stage but gives me the overall data set min date when I remove transaction number to group by customer bill to site

I also tried

min(CASE WHEN "Financials - AR Transactions"."Facts - Analytics Currency"."Remaining Amount" <> 0 THEN "Financials - AR Transactions"."Transaction Creation Date"."Transaction Creation Date" ELSE CAST('2049-12-31' AS Date ) END BY "Financials - AR Transactions"."Customer Bill-to Details"."Site Id" ) but that then returns a date that isn't even in my data set.

Richard Halford

I think I have got to an answer looking at the SQL developed and its because of using Paying Customer. It looks like that focuses the query on sub ledger accounting, so if I use the customer and for site bill to site from AR transactions I can then pull in the invoice details. So now I can do a customer account reconciliation report against all invoices.

The only bit missing is dispute amounts to look at next.