Set Analysis Troubles

[#1]

Trying to show information on a charts for works orders that are due before the due date of sales orders.

In my expression I have set the following definition

sum({$<wo_req_date<sales_required_date>} req_qty_outstanding)

This is returning a value, but for all the data, not orders due before the due date of the sales order.

Sales Data

6G33-L611B11-BE	28/03/2011	14	14	56	80
6G33-L611B11-BE	04/04/2011	7	21	56	80
6G33-L611B11-BE	11/04/2011	14	35	56	80
6G33-L611B11-BE	09/05/2011	21	56	56	80
6G33-L611B11-BE	16/05/2011	21	77	56	80
6G33-L611B11-BE	13/06/2011	42	119	56	80
6G33-L611B11-BE	11/07/2011	21	140	56	80
6G33-L611B11-BE	15/08/2012	7	147	56	80

Works Order Data

6G33-L611B11-BE	WO-067177	13/08/2010	8	8
6G33-L611B11-BE	WO-067643	18/04/2011	72	80

I would expect to see 80 being displayed in the last column of Sales Data as the two works orders are due before 09/05/11 and nothing for the remainder.

Any Ideas?

[Miguel_Angel_Baeyens]

Hello,

Are "wo_req_date" and "sales_required_date" fields of the same record?

Then try

sum({$< wo_req_date = {'<=$(=Only(sales_required_date))'} >} req_qty_outstanding)

Or something similar. Note that the date format for both must be the same.

Hope that helps.

[#3]

The Dates are the same format but from different tables, the product is the only common link

[#4]

Using the suggestion above '80' is populated in every field which isn't what I require. Also if I hard code the sales_required_date to 15/04/11 the value returned is still '80' what I would expect '8' to be displayed

[#5]

I'm assuming now what I'm trying to do isn't possible.

Can anybody confirm?

[Miguel_Angel_Baeyens]

Hello and sorry for not getting back to you earlier.

It would be easier if you post some sample data we can work with, and what results do you expect. I understand that you want to count how many work orders are due regarding their dates, but it's not clear to me how that fits in your datamodel.

Regards.

[#7]

I have added a qvw file which should have enough data in it.

I have been looking further into the problem this morning and it is sort of working, but you have to select a date from top chart to get the result which is correct, but of course filters the data which isn't what I require.

What I am trying to achieve though is a row by row analysis comparing the works order due date (wo_req_date) with the sales due date (sales_required_date) to establish stock levels.

thanks

[Miguel_Angel_Baeyens]

Hi,

Without using set analysis, would this be the right value?

sum(If(wo_req_date < sales_required_date, req_qty_outstanding, 0))

[#9]

Thanks, I'm sure I tried that, but must have got the syntax wrong.

Many Thanks