2 Replies Latest reply: Dec 16, 2015 3:19 PM by Andii Toole RSS

    Multiple AND/OR Statements in Set Analysis

      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)

       

      InvoiceIDInvoiceCreatedDateInvoiceAmountCurrent BalanceStatusCloseDate
      11/1/2015100.000.00Closed

      10/1/2015

      25/1/2015100.0095.00Open-

       

      InvoiceIDPROIDPROAmountPaidPROCreatedDate
      110010.002/1/2015
      21015.007/1/2015

       

      InvoiceIDPMPIDPROAmountPaidPROCreatedDate
      1100090.0010/1/2015
      2100150.009/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:

      InvoiceIDInvoiceCreatedDateInvoiceAmountCurrentStatusBalance as of (vVariableDate)
      11/1/2015100.00Closed90.00

       

      If I were to enter 9/1/2015 I would get:

      InvoiceIDInvoiceCreatedDateInvoiceAmountCurrentStatusBalance as of (vVariableDate)
      11/1/2015100.00Closed90.00
      25/1/2015100.00Open45.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?