Problem in Writing MDX: Help ????

• shagarwal

  SSC-Addicted

  Points: 461

  More actions

  March 3, 2006 at 8:56 am

  #112975

  I  have 2 dimensions Loans and Indicatordate in my database. 

  Loan

  --Loan ID

  IndicatorDate

  --Year

  ---Month

  ----Day

   

  I have written a function that returns me nearest Date from my database in case the date specified by the user does not exists.

  The function applies to only those Loans for which the Indicator date is same as the Date entered my the user. It does not apply to all the loan Ids.

  I want this function to return Indicator dates against all the Loan Ids and in case for a partcular Loan ID  the date entered by the user does not exists then then i should get the Nearest 'Previous' existing Date for that Loan ID.

  I want to know how can i apply this function to all the members of the Loan dimension ??

  I have used "Head", along with Not Exists and Count functions  as follows

  Select

   {[Measures].[Arrear30to59]}  on 0,

   

  {Head (Filter (Descendants([LoansIndicatorDate].[All LoansIndicatorDate].children,3),NOT IsEmpty([LoansIndicatorDate].CurrentMember.Name="25" and [LoansIndicatorDate].Parent.Name="November" and [LoansIndicatorDate].parent.parent.Name="2005")),1).item(0)} on 1

   

  from Loans

  Please help on this.

   

• March 6, 2006 at 8:00 am

  This was removed by the editor as SPAM

• shagarwal

  SSC-Addicted

  Points: 461

  More actions

  March 23, 2006 at 9:29 am

  #628389

  Finally after a lot of R&D i found one solution here it is :

  with member

  [Measures].[CloseBal] as '([Measures].[Savings Portfolio],

     ClosingPeriod ( [SavingsBalanceDate].[Day], [SavingsBalanceDate].CurrentMember))'

  member

  [Measures].[Savings Balance] as ' CoalesceEmpty(

  ([Measures].[CloseBal], [SavingsBalanceDate].CurrentMember),

  ([Measures].[Savings Balance], [SavingsBalanceDate].currentmember.prevmember)

  ) '

  select {[Measures].[CloseBal],

  [Measures].[Savings Balance]} on 0 ,

  {Descendants([SavingsBalanceDate].[All SavingsBalanceDate].[2005].[Quarter 4].[December].children,0)} on 1

  from loansandsavings where ([Accounts].[All Accounts].[Savings].[2010000001],[LoansSavingsCurrentPn].[All LoansSavingsCurrentPn].[0])

  Using CoalesceEmpty recusively i was able to get balance amount for all the dates

  thanks ..