topic Re: Is a set analysis workaround possible in this case? in QlikView

Is a set analysis workaround possible in this case?

Mon, 04 Apr 2016 11:43:47 GMT

Hello,

I have a concatenated fact table which looks like this:

OrderLineNumber	DeliveryLineNumber	OrderLineValue	DeliveryLineValue
1\|1		100
1\|2		200
2\|1		50
1\|1	9001\|1		80
1\|2	9001\|2		200
2\|1	9002\|1		30

I also have two other tables for orders and deliveries, which look like this:

- Orders:

OrderLineNumber	RequestedDeliveryDate
1\|1	04/04/2016
1\|2	04/04/2016
2\|1	01/05/2016

- Deliveries:

DeliveryLineNumber	DeliveryLineStatus
9001\|1	Shipped
9001\|2	Shipped
9002\|1	InProcess

In my application, I have a tab with a table which should look like this:

OrderLineNumber	OrderLineValue	Sum({$<[DeliveryLineStatus]={'Shipped}>}DeliveryLineValue)	Sum({$<[DeliveryLineStatus]={'InProcess'}>}DeliveryLineValue)
1\|1	100	80	0
1\|2	200	200	0
2\|1	50	0	30

I also have a listbox with the month number of the RequestedDeliveryDate you find in the orders table.

My problem is when I select a month in the calendar I just mention, the last two columns in my tab show 0. I understand why, but can I work around this problem with a set analysis, or should I modify my fact table?

I'm looking into the p() function, but I'm not sure this is correct.

Re: Is a set analysis workaround possible in this case?

swuehl — Mon, 04 Apr 2016 11:51:13 GMT

You could try something like

=Sum( {1<[DeliveryLineStatus]={'Shipped}, OrderLineNumber = p() >} DeliveryLineValue)

Re: Is a set analysis workaround possible in this case?

Gysbert_Wassenaar — Mon, 04 Apr 2016 11:56:26 GMT

I think I'd put the OrderLineValue field in the Orders table together with the corresponding OrderLineNumber and RequestedDeliveryDate.

Re: Is a set analysis workaround possible in this case?

Mon, 04 Apr 2016 12:10:21 GMT

Interesting... can I ask why you would do that?

Re: Is a set analysis workaround possible in this case?

Mon, 04 Apr 2016 12:11:15 GMT

It looks like it is working. Let me test a little bit more before I flag this answer.

Re: Is a set analysis workaround possible in this case?

Gysbert_Wassenaar — Mon, 04 Apr 2016 12:13:26 GMT

I don't see any reason why that field should be added to the fact table. That only seems to add extra rows to the fact table which then also cause the problem you now have.