Working with a distribution problem in MDX for my fact table.
There is a shipped fact table with order numbers; there is also a confirmed table with order numbers correspond to the shipped table however, confirmed qty vs. shipped qty can be different ---- basically what the amount we ship to a customer [Measures].[Shipped Qty] and what they confirmed as what they received [Measures].[Confirmed Qty] can be different.
I want to find this difference but projecting/filtering the confirmed orders onto the shipped --- basically exclude any orders from the shipped qty that have NOT been confirmed.
There is an order dimension which structure of attribute [Order].[Order No].[Order No] that I'd like to use. I'm trying to create this new quantity as a Calculated Member in the cube for users.
Here's what I've been trying to do so far:
WITH
SET [Confirmed Orders] AS
{
FILTER([Order].[Order No].[Order No].MEMBERS, [Measures].[Confirmed Qty] > 0.0000 )
}
MEMBER [Measures].[Confirmed Shipped Qty] AS
(
Aggregate( [Confirmed Orders], [Measures].[Shipped Qty])
)
SELECT { [Measures].[Shipped Qty], [Measures].[Confirmed Qty], [Measures].[Confirmed Shipped Qty] } ON COLUMNS
, {NONEMPTYCROSSJOIN([Date].[Yr - Qtr - Mo - Wk - Date].[Year].[2010],[Date].[Month].[Month]} ON ROWS
FROM [MyCube]
As you can see I'm only doing this for 2010 by month. In SQL I would INNER JOIN these two tables (FactShipped and FactConfirmed) on OrderNo in order to find that confirmed shipped quantity; tryin to figure out how to do this in MDX. Any insight would be greatly appreciated and hope this question was easy to comprehend!