MDX HELP Pls

View previous topic View next topic Go down

MDX HELP Pls

Post  nachikethm on Wed Apr 04, 2012 8:34 am



We have the following Dimensions and Fact tables:

1. DimDate
2. DimHouse(It can have both Active and Prospect House holds)
3. FactAtive this contains number of bookings
4. FactPropect

We need to write a MDX query to get sum of booking for every month from the context of the prospect fact table. So if we look at jan 2007 we want to get the sum of booking from the FactActive Table, but for period in the future, for example lead of two months, meaning March 2007 in the factActive table, but for all house hold in the Factprospect table. In sql we would do a sub query which will give all the house holds from the FactProspect table for month jan 2007.. than filter the factActive table based on this sub query.

For example: 100 Prospects in Jan 2007 out of which 30 become Active in March.

From SQL tables:
SELECT SUM(a.NumOfBookingThisMonth)
FROM dbo.FactHouseholdMonthActive a
JOIN dbo.DimHouseHoldType h
ON h.HouseHoldTypeSurKey = a.HouseHoldTypeSurKey
WHERE 1 = 1
AND a.MonthEndDateKey BETWEEN 20070228 AND 20070331
AND h.HouseHoldSubTypeName = 'Prospect Conversion'
AND EXISTS ( SELECT p.HouseHoldSurKey
FROM dbo.FactHouseholdMonthProspect p
WHERE p.MonthEndDateKey = 20070131
AND p.HouseHoldSurKey = a.HouseHoldSurKey
AND p.BrandSurkey = a.BrandSurkey )


We have tried to use exist function but we have had no success.

MDX Query :

with member [Measures].[1-2 MOnths] as
sum(
(
existing(
[Household].[House Hold Sur Key].[House Hold Sur Key].members
),
( [Date].[Dates].Currentmember.Lead(1):[Date].[Dates].Currentmember.lead(2),[HouseHoldType].[House Hold Sub Type Name].&[Prospect Conversion]
)
,[Measures].[Num Of Booking This Month]
))

select {[Measures].[1-2 MOnths]

} on columns
,[Date].[Dates].[Month].members on rows
from [MonthendStatus]
where ([Date].[Calendar Year].&[2007]:[Date].[Calendar Year].&[2014])

Thanks
Nachi

nachikethm

Posts : 1
Join date : 2012-04-04

View user profile

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum