Good Morning Everyone,
I am trying to build a report for my CFO, and he needs to see what was owed at certain points in time in Accounts Receivable.
In order to do this, I need to take data that looks like this: (Just a couple of the tables in order to give you an idea)
| InvoiceID | InvoiceCreatedDate | InvoiceAmount | Current Balance | Status | CloseDate |
|---|
| 1 | 1/1/2015 | 100.00 | 0.00 | Closed | 10/1/2015 |
| 2 | 5/1/2015 | 100.00 | 95.00 | Open | - |
| InvoiceID | PROID | PROAmountPaid | PROCreatedDate |
|---|
| 1 | 100 | 10.00 | 2/1/2015 |
| 2 | 101 | 5.00 | 7/1/2015 |
| InvoiceID | PMPID | PROAmountPaid | PROCreatedDate |
|---|
| 1 | 1000 | 90.00 | 10/1/2015 |
| 2 | 1001 | 50.00 | 9/1/2015 |
and make it see a specific date in time and subtract all the possible payment types from the original invoice amount that were possible during on or before that date.
Basically, in order to look at any given day (which I created as an input variable) I need to combine the following or/and statements: (in plain language)
IF(
(InvoiceCreatedDate is greater than (vVariableDate))
AND (InvoiceCloseDate is null or greater than (vVariableDate))
AND (PRODate is null or less than (vVariableDate))
AND (PMPDate is null or less than (vVariableDate))
AND (DirectPayDate is null or less than (vVariableDate))
AND (WriteOffDate is null or less than (vVariableDate))
AND (AppliedDiscountDate is null or less than (vVariableDate))
AND (ManualAdjustmentDate is null or less than (vVariableDate))
THEN SUM(InvoiceAmount)-SUM(PROAmount + PMPAmount + DirectPayAmount + WriteOffAmount + AppliedDiscountAmount + ManualAdjustmentAmount).
In the simplified tables above, basically if I input 4/1/2015 into the variable date field I would get:
| InvoiceID | InvoiceCreatedDate | InvoiceAmount | CurrentStatus | Balance as of (vVariableDate) |
|---|
| 1 | 1/1/2015 | 100.00 | Closed | 90.00 |
If I were to enter 9/1/2015 I would get:
| InvoiceID | InvoiceCreatedDate | InvoiceAmount | CurrentStatus | Balance as of (vVariableDate) |
|---|
| 1 | 1/1/2015 | 100.00 | Closed | 90.00 |
| 2 | 5/1/2015 | 100.00 | Open | 45.00 |
I was planning on creating a simple flag (IF(ISNULL(PRODATE),0,1) etc.) for whether or not any of those dates are null to make it a little simplified, but how do I do this combination of AND/OR statements in Set Analysis? Is it possible?
