Combining logical operators in set analysis

[#1]

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 · Accepted solution]

Hi

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]

Wain,

try this

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

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

[#4]

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

[#5]

Thanks Santharubban as well