Sales , with annulations taken into account?

[#1]

Hey all,

I am struggling with a QV development problem for a week now. Now I ask for your help.

Attached you can find an extract of the sales database of 2010. In the reporting I want to build, I want to compare the sales figures by month, but it should be the sales as it was at the time.

An example will clarify this:

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)

If I select the month September, I would like to see : Sales Amount : 150 (order BBB) - 120 (line 2 of order AAA, which was cancelled in September).

If I select the months January till September , I would like to see : 240 (order AAA) + 150 (order BBB)

Can anyone tell me how to make this happen?

Thanks in advance !

Jef

[IAMDV]

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.

Your comment:

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,

DV

[#3]

D V,

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 ...

Greetz,

Jef

[pat_agen · Accepted solution]

hi,

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

[#5]

Thanks pat.agen,

this looks like something that might wordk for me.

I'm gonna try this in my report.

Greetings,

Jef