Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
sebastianlettne
Contributor

Re: Multiple AND/OR Statements in Set Analysis

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

2 Replies
sebastianlettne
Contributor

Re: Multiple AND/OR Statements in Set Analysis

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

Not applicable

Re: Multiple AND/OR Statements in Set Analysis

This worked beautifully, thank you!

Community Browser