Crossed Sales Problem

[cafcptg2011]

Hello,

I have a problem with crossed sales (as I called).

I have 2 tables:

     - one is about the sales

     - the other one is about the requests

So, what I need is to get sales after request dates of the produt "a", like I show in my third table.

After 06-03-2012 request I have 4 sales and after 20-03-2012 I have 1 sale. Can anyone help me please to accomplish this, until now didn't figure out one way to get the correct results.

Thanks in advance,

cafc

Note: I have attached a qvw with my tables to understand better the problem.

[#2]

Hi cafc,

You could use a nested if statment in the expression to work out how many sales were after the request date:

=sum(if(SalesDate >= RequestDate,Sales))

Regards,

Erica

[cafcptg2011]

Hi Erica,

First of all thanks for your reply. I have tried your solution :

The sales quantity after 06-03-2012 request date are 4 sales indeed and it is correct. But I don't understand why gives 2 sales quantity after 20-03-2012, it should be 1 sale quantity and not 2, rigth?

thanks again

cafc

[#4]

Sadly I can't open your document as I am at home and only have the trial version, so I can only speculate

It is unusual that it is returning 2. What is the source data like?

If the request date data looks like this

Request Date	Requests
20/03/12	1
20/03/12	1

Then it might join the single sale twice onto the end, and count it twice, if you know what I mean.

HMMM!

Could you give me an idea of what the source tables look like so I can try it here?

Regards,

Erica

[cafcptg2011]

yes, sure.

Requests:

load * inline

[

store, produt, RequestQty, RequestDate

1, a, 2,'06-03-2012'

1, b, 1,'03-03-2012'

1, c, 1,'03-03-2012'

1, d, 1,'03-03-2012'

1, d, 1,'08-03-2012'

2, c, 1,'08-03-2012'

2, a, 1,'09-03-2012'

2, a, 1,'09-03-2012'

2, a, 1,'10-03-2012'

2, d, 1,'06-03-2012'

3, a, 2,'06-03-2012'

3, a, 1,'20-03-2012'

3, a, 1,'20-03-2012'

3, b, 1,'22-03-2012'

]

;

Sales:

load * inline

[

store, produt, SalesQty, SalesDate

1, a, 2,'09-03-2012'

1, a, 1,'06-03-2012'

1, b, 1,'03-03-2012'

1, c, 1,'03-03-2012'

1, d, 1,'03-03-2012'

1, d, 1,'01-03-2012'

2, c, 1,'09-03-2012'

2, a, 1,'09-03-2012'

2, a, 1,'10-03-2012'

2, a, 1,'16-03-2012'

2, d, 1,'16-03-2012'

3, a, 2,'17-03-2012'

3, a, 1,'19-03-2012'

3, a, 1,'20-03-2012'

3, b, 1,'21-03-2012'

3, b, 1,'24-03-2012'

]

;

And my doc qvw looks like this:

indeed there 2 requests on 20-3-2012 but why it " joined the single sale twice onto the end, and count it twice" as you said??

thanks,

Regards,

Cafc

[#6]

I'm bumping this back up because it's had me stumped, even when I tried to replicate it at home! Set analysis doesn't work either 😐

Any one else have any insight? Of Cafc did you solve the problem?

[swuehl]

Try maybe

=sum(aggr(if(SalesDate >= RequestDate,SalesQty),RequestDate,SalesDate))

as expression in the Sales after Requests table chart.

Regards,

Stefan

[cafcptg2011 · Accepted solution]

PERFECT!!

thanks both of you!