Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You could try something like
=Sum( {1<[DeliveryLineStatus]={'Shipped}, OrderLineNumber = p() >} DeliveryLineValue)
You could try something like
=Sum( {1<[DeliveryLineStatus]={'Shipped}, OrderLineNumber = p() >} DeliveryLineValue)
I think I'd put the OrderLineValue field in the Orders table together with the corresponding OrderLineNumber and RequestedDeliveryDate.
Interesting... can I ask why you would do that?
It looks like it is working. Let me test a little bit more before I flag this answer.
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.