I have 2 dates in my cube Indicator Date , Disbursed Date .
If user enters 2 dates:
1) Indicator From Date
2) Indicator To Date
I want sum of Principal Amount between the 2 dates and Arrear position at the
Indicator To Date .
If the Arrear Amount does not exists for that Date then i want the Previous Date
My cube contains data on the account level.
I want to have an aggregate on the Product Level.
I am able to get sum of Principal Amount for all the Accounts disbursed between the 2 dates at the product level but im not able to get the
Arrear Amount Based on the Indictaor to Date.
I am trying to do a semi-additive measure in a cube and i am using the
following MDX to get a closing balance.
First i get the closing balances:
[Measures].[Opening Amount in Arrears] as '([Measures].[Arrear1to8],
OpeningPeriod ( [LoansIndicatorDate].[Day], [LoansIndicatorDate].CurrentMember))'
and then i wrote this MDX to get the last known value in case the
value for current time is not there yet(I sometimes don't get data for
all the products for previous day, In that case i have to display the
previous known value):
[Measures].[Relative Total Arrears Amount] as ' CoalesceEmpty(
([Measures].[Opening Amount in Arrears], [LoansIndicatorDate].CurrentMember),
([Measures].[Relative Total Arrears Amount], [LoansIndicatorDate].currentmember.prevmember))'
With CoalesceEmpty function i am able to get the result correct at the account level.
However CoalesceEmpty does not give me correct result on Product Level where Products consists of many Accounts. (ie. at a higher level)