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

Question

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

Richard Halford · Answer

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.

Richard Halford · Answer

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 · Answer

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