topic Set Analysis Troubles in QlikView

Set Analysis Troubles

Mon, 28 Mar 2011 12:03:49 GMT

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?

Set Analysis Troubles

Miguel_Angel_Baeyens — Mon, 28 Mar 2011 12:10:48 GMT

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.

Set Analysis Troubles

Mon, 28 Mar 2011 12:46:16 GMT

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

Set Analysis Troubles

Mon, 28 Mar 2011 15:02:49 GMT

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

Set Analysis Troubles

Tue, 29 Mar 2011 09:43:06 GMT

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

Can anybody confirm?

Set Analysis Troubles

Miguel_Angel_Baeyens — Tue, 29 Mar 2011 09:46:42 GMT

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.

Set Analysis Troubles

Tue, 29 Mar 2011 10:04:15 GMT

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

Set Analysis Troubles

Miguel_Angel_Baeyens — Tue, 29 Mar 2011 10:27:17 GMT

Hi,

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

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

Set Analysis Troubles

Tue, 29 Mar 2011 10:48:41 GMT

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

Many Thanks