|
| I have a Cube on Loans ; having 2 date dimensions :-
Indicator Date , Disbursed Date .
If user enters 2 dates:
1) From Date
2) To Date
I want sum of Principal Amount between the 2 dates and Arrear position on
the To Date .
If the Arrear Amount does not exists for that Date then i want the Previous
Date
Arrear Amount.
My cube contains data on the account level.
I want to have an aggregate on the Product Level.
Initially the problem was that we could not cuse both the date dimesnsions
so to get around this problem , we merged both the time dimensions and used
only one dimension.
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 "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 ( & #91;LoansIndicatorDa
te].[Day],
& #91;LoansIndicatorDa
te].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):
member
[Measures].[Relative Total Arrears Amount] as ' CoalesceEmpty(
([Measures].[Opening Amount in Arrears], & #91;LoansIndicatorDa
te].CurrentMember),
([Measures].[Relative Total Arrears Amount],
& #91;LoansIndicatorDa
te].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)
|
|