Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Wain,
try this
sum({$<[Invoice Posted Date] = {"<=$(vReportdate)"}, [Invoice State] -= {'Cancelled'}> } [Invoiced QTY]) +
sum({$<[Invoice State] = {'Cancelled'}, [Cancelled Date] = {"<=$(vReportdate)"}>} [Invoiced QTY])
Thanks Jonathan for your quick reply. I will try that now.
Thanks Santharubban as well