Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
sebastianlettner

Hi,

you can use set analysis operators ('+' like 'OR' and '*' like 'AND'). So your expression would look like

Sum({$ <InvoiceCreatedDate={">$(vVariableDate)"}> *

            (<InvoiceCloseDate-={"=len(trim(InvoiceCloseDate))=0"}> + <InvoiceCloseDate={">$(vVariableDate)"}>) *

            (<PRODate-={"=len(trim(PRODate))=0"}> + <PRODate={"<$(vVariableDate)">) *

            (<...> + <...> ) * (.......)} InvoiceAmount - (PROAmount + PMPAmount + DirectPayAmount + WriteOffAmount + AppliedDiscountAmount + ManualAdjustmentAmount))



Regards

Sebastian Lettner

View solution in original post

2 Replies
sebastianlettner

Hi,

you can use set analysis operators ('+' like 'OR' and '*' like 'AND'). So your expression would look like

Sum({$ <InvoiceCreatedDate={">$(vVariableDate)"}> *

            (<InvoiceCloseDate-={"=len(trim(InvoiceCloseDate))=0"}> + <InvoiceCloseDate={">$(vVariableDate)"}>) *

            (<PRODate-={"=len(trim(PRODate))=0"}> + <PRODate={"<$(vVariableDate)">) *

            (<...> + <...> ) * (.......)} InvoiceAmount - (PROAmount + PMPAmount + DirectPayAmount + WriteOffAmount + AppliedDiscountAmount + ManualAdjustmentAmount))



Regards

Sebastian Lettner

View solution in original post

Not applicable
Author

This worked beautifully, thank you!