Calculate Due Date Using Days Late

Question

Hello,

I need the correct information about Days Late so that I can calculate the due date. I am not using the Receivables - Standard Receipts Application Details Real Time subject area because I am experiencing several issues trying to pull the other necessary information from this area.

Below is an example: the application shows 17 days late, while OTBI only returns 11 days late.

How can I perform this operation? Is there an alternative way to obtain the due value?

Currently, I am using the following calculation to account for weekends:

TimestampAdd(SQL_TSI_DAY,

ABS("Standard Receipt Application Details"."Receivable Application Days Late") +

FLOOR(ABS("Standard Receipt Application Details"."Receivable Application Days Late") / 5) * 2,

"Standard Receipt Details"."Deposit Date")

Nathan CCC · Answer

Hi,

No problem. So you question is actually the classic when I build my analysis by picking attributes and measures from multiple subject areas how do we get the metadata repository database (RPD) data model to generate "good" physical SQL with the correct joins between the application tables and views so that we get the expected results from our query.

You can experiment by trying to guess what combination of attribute columns and fact measure columns are needed from each subject area for any conformed dimensions across the star schemas. Keep going to page issue raw sql or page manage sessions to view log to see if it is doing what you want it to do or not.

Or alternative you can edit the logical sql with sub queries with explicit join syntax to tell the system what attributes you want it to use to join subject area A and B. Then with your custom logical sql either "create analysis from simple logical sql" or create a report data model with data source Oracle BI EE. Again view log to validate.

Kauan_Bohn · Answer

I understand this; however, without the due date, my report is inefficient, and the subject areas I mentioned above do not communicate well.

The due value is for the installment, but if I include the Due Date field from the Receivables - Payment Schedules Real Time subject area, I still face issues.

Below is a screenshot showing that this information is not coming through to my analysis:

Nathan CCC · Answer

Hi Kauan,

"Due Date" is not a calculated field. It is a data entry field entered into the system using a date picker when you save and close the payment schedule on the one or more instalment(s) on a receivables transaction. The default value may get calculated but you as a receivables specialist may add instalments and update dues dates on any of them. Therefore if you do do some sort of calculation in your query then I strongly recommend you do not call it "due date" because it will a different date that may not match what on your transaction so you need to avoid confusion for your consumers.