topic Re: Crossed Sales Problem in QlikView

Crossed Sales Problem

cafcptg2011 — Sat, 07 Apr 2012 14:59:48 GMT

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.

Crossed Sales Problem

Sat, 07 Apr 2012 15:41:07 GMT

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

Re: Crossed Sales Problem

cafcptg2011 — Sat, 07 Apr 2012 17:29:52 GMT

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

Re: Crossed Sales Problem

Sat, 07 Apr 2012 19:05:39 GMT

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

Re: Crossed Sales Problem

cafcptg2011 — Sat, 07 Apr 2012 19:19:51 GMT

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,'10-03-2012'

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

3, a, 2,'06-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

Re: Crossed Sales Problem

Sun, 15 Apr 2012 12:45:31 GMT

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?

Crossed Sales Problem

swuehl — Sun, 15 Apr 2012 13:10:19 GMT

Try maybe

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

as expression in the Sales after Requests table chart.

Regards,

Stefan

Crossed Sales Problem

cafcptg2011 — Sun, 15 Apr 2012 13:45:03 GMT

PERFECT!!

thanks both of you!