Jef - Before I understand the requirement. I have couple of questions...
1. how did you arrive amount as 120 . Your excel file does not show up any amount or quanity to calculate the amount.
If I select the month May, I want to see : Sales amount 0 + 120 + 210 + 30 = 360 (because line1 of order AAA is cancelled in May, and line 2 is only cancelled in September)
2. You wanted to exclude the canceled amount right? Let's say for example the first row order is placed in May but cancelled in September. So what do you want to see when May, June, July & August are selected?
Sorry I need more explanation.
thanks for your reply. Here are the answers to your questions :
1. the amount for line2 of order AAA is calcluted like this : Quantity cancelled 10 * Unit Price 12 = 120
2. When I select May, June, July & August: there should be an amount for the orderline 2, 3 and 4 of order AAA. Order line 1 should have no amount, as it is cancelled in May
I hope this makes it more clear for you ...
I would rework your input data to simplify matters.
Load your Orders with quantity column filled in. Then concatenate to this table the cancellations with quantity reversed out. Your expressions will be simply sums.
see the attached qv for a way of doing this.
I have left the input data as is so you compare the before and after
SalesWithAnnulations.qvw 118.6 K