topic Re: Combining logical operators in set analysis in QlikView

Combining logical operators in set analysis

Thu, 03 Apr 2014 13:31:42 GMT

The following expression works OK but does not handle cancelled invoices (where [Invoice State] = 'Cancelled')

sum({$<[Invoice Posted Date] = {"<=$(vReportdate)"}>}[Invoiced QTY])

The problem is that even if they are cancelled I need to include them if [Cancelled Date] > vReportdate

I need to build in the logic:

[Invoice State] <> 'Cancelled' or ([Invoice State] = 'Cancelled' AND [Cancelled Date] > vReportdate)

or written another way

NOT( [Invoice State] = 'Cancelled' and [Cancelled Date] <= vReportdate)

The report date can be amended by the end user to be any date in the past or it defaults to today

Can anyone help please?

jonathandienst — Thu, 03 Apr 2014 13:40:16 GMT

Create the two elements in <> brackets and union them:

sum({$<[Invoice Posted Date] = {"<=$(vReportdate)"}, [Invoice State] -= {'Cancelled'}> +

<[Invoice State] = {'Cancelled'}, [Cancelled Date] = {"<=$(vReportdate)"}>} [Invoiced QTY])

Add any extra terms to each side if required to ensure that there is no double counting.

HTH

Jonathan

santharubban — Thu, 03 Apr 2014 13:47:50 GMT

Wain,

try this

sum({$<[Invoice Posted Date] = {"<=$(vReportdate)"}, [Invoice State] -= {'Cancelled'}> } [Invoiced QTY]) +

sum({$<[Invoice State] = {'Cancelled'}, [Cancelled Date] = {"<=$(vReportdate)"}>} [Invoiced QTY])

Thu, 03 Apr 2014 13:48:27 GMT

Thanks Jonathan for your quick reply. I will try that now.

Thu, 03 Apr 2014 13:50:34 GMT

Thanks Santharubban as well