Multiple AND/OR Statements in Set Analysis

Tue, 15 Dec 2015 17:23:32 GMT

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?

Re: Multiple AND/OR Statements in Set Analysis

sebastianlettner — Wed, 16 Dec 2015 09:00:50 GMT

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

Re: Multiple AND/OR Statements in Set Analysis

Wed, 16 Dec 2015 20:19:48 GMT

This worked beautifully, thank you!

topic Re: Multiple AND/OR Statements in Set Analysis in QlikView

Multiple AND/OR Statements in Set Analysis

Re: Multiple AND/OR Statements in Set Analysis

Re: Multiple AND/OR Statements in Set Analysis