Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Is a set analysis workaround possible in this case?

Hello,

I have a concatenated fact table which looks like this:

OrderLineNumberDeliveryLineNumberOrderLineValueDeliveryLineValue
1|1100
1|2200
2|150
1|19001|180
1|29001|2200
2|19002|130

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

- Orders:

OrderLineNumberRequestedDeliveryDate
1|104/04/2016
1|204/04/2016
2|101/05/2016

- Deliveries:

DeliveryLineNumberDeliveryLineStatus
9001|1Shipped
9001|2Shipped
9002|1InProcess

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

OrderLineNumberOrderLineValueSum({$<[DeliveryLineStatus]={'Shipped}>}DeliveryLineValue)Sum({$<[DeliveryLineStatus]={'InProcess'}>}DeliveryLineValue)
1|1100800
1|22002000
2|150030

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You could try something like

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

View solution in original post

5 Replies
swuehl
MVP
MVP

You could try something like

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand