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 ( [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):



member

[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)

Thanks in Advance.

Shuchi.

Assuming that there is a [Product] dimension with a leaf [Account]
level, and that you wish to sum up the [Account] level contributions to
the semi-additive [Relative Total Arrears Amount] measure:


[Measures].[Relative Total Arrears Amount] as
'iif(IsLeaf([Product].CurrentMember, CoalesceEmpty(
([Measures].[Opening Amount in Arrears],
[LoansIndicatorDate].CurrentMember),
([Measures].[Relative Total Arrears Amount],
[LoansIndicatorDate].currentmember.prevmember)),
Sum(Descendants([Product].CurrentMember,,LEAVES),
[Measures].[Relative Total Arrears Amount]))'

agarwalshuchi (AT) gmail (DOT) com wrote:

Thanks for your prompt reply .

But its not giving be correct sum For Products.

I have 3 different dimensions Products , Accounts , Indicator Date
i have one measure : Arrear1to8

I am getting correct result for individual Accounts but not getting
correct sum at the Product level.

i tried the following Query:-

member

[Measures].[Opening Amount in Arrears] as '([Measures].[Arrear1to8],
OpeningPeriod ( [LoansIndicatorDate].[Day],
[LoansIndicatorDate].CurrentMember))'

member

[Measures].[Relative Total Arrears Amount] as ' Sum
({[Products].currentmember},CoalesceEmpty(
([Measures].[Opening Amount in Arrears],
[LoansIndicatorDate].CurrentMember),
([Measures].[Relative Total Arrears Amount],
[LoansIndicatorDate].currentmember.prevmember)))'


Select

{Measures].[Relative Total Arrears Amount]} on 0,

{[Products].[All Products].[Loans].Children} on 1

from

LoansAndSavings

where

([LoansIndicatorDate].[All LoanIndicators].[2004].[Quarter
4].[November].[30])


My Data is like :

ProductType Account 1D IndicatorDate Arrear1to8
1 01 11-2-2004 80
1 01 11-15-2004 50
1 01 11-28-2004 100
1 02 11-13-2004 50
2 03 11-2-2004 30


For a Single ProductType "1" i have 2 acccountID 01,02

for Account ID "01" Previous existing Date is IndicatorDate 11-28-2005
.. Arrear Amount is 50
For Account ID "02" Previous existing Date is IndicatorDate 11-2-2005 .
Arrear Amount is 100

I want their sum at the ProductType level i.e . 150 and not just 50. It
gives correct results for
each account but its not summing the accounts :

I get the follwoing result:

ProductType Arrear1to8
1 50
2 30

For Product type 1 i should get 150 and not 50.

Thanks again .

Shuchi.

Thanks for your prompt reply .


But its not giving be correct sum For Products.


I have 3 different dimensions Products , Accounts , Indicator Date
i have one measure : Arrear1to8


I am getting correct result for individual Accounts but not getting
correct sum at the Product level.


i tried the following Query:-


member


[Measures].[Opening Amount in Arrears] as '([Measures].[Arrear1to8],
OpeningPeriod ( [LoansIndicatorDate].[Day],
[LoansIndicatorDate].CurrentMember))'


member


[Measures].[Relative Total Arrears Amount] as ' Sum
({[Products].currentmember},CoalesceEmpty(
([Measures].[Opening Amount in Arrears],
[LoansIndicatorDate].CurrentMember),
([Measures].[Relative Total Arrears Amount],
[LoansIndicatorDate].currentmember.prevmember)))'


Select


{Measures].[Relative Total Arrears Amount]} on 0,


{[Products].[All Products].[Loans].Children} on 1


from


LoansAndSavings


where


([LoansIndicatorDate].[All LoanIndicators].[2004].[Quarter
4].[November].[30])


My Data is like :


ProductType Account 1D IndicatorDate Arrear1to8
1 01 11-2-2004 80
1 01 11-15-2004 50
1 01 11-28-2004 100
1 02 11-13-2004 50
2 03 11-2-2004 30


For a Single ProductType "1" i have 2 acccountID 01,02


for Account ID "01" Previous existing Date is
IndicatorDate 11-28-2005 . Arrear Amount is 50

For Account ID "02" Previous existing Date is IndicatorDate 11-2-2005 .

Arrear Amount is 100


I want their sum at the ProductType level i.e . 150 and not just 50. It

gives correct results for
each account but its not summing the accounts :


I get the follwoing result:


ProductType Arrear1to8
1 50
2 30


For Product type 1 i should get 150 and not 50.


Thanks again .


Shuchi.

Thanks for your prompt reply .


But its not giving be correct sum For Products.


I have 3 different dimensions Products , Accounts , Indicator Date
i have one measure : Arrear1to8


I am getting correct result for individual Accounts but not getting
correct sum at the Product level.


i tried the following Query:-


member


[Measures].[Opening Amount in Arrears] as '([Measures].[Arrear1to8],
OpeningPeriod ( [LoansIndicatorDate].[Day],
[LoansIndicatorDate].CurrentMember))'


member


[Measures].[Relative Total Arrears Amount] as ' Sum
({[Products].currentmember},CoalesceEmpty(
([Measures].[Opening Amount in Arrears],
[LoansIndicatorDate].CurrentMember),
([Measures].[Relative Total Arrears Amount],
[LoansIndicatorDate].currentmember.prevmember)))'


Select


{Measures].[Relative Total Arrears Amount]} on 0,


{[Products].[All Products].[Loans].Children} on 1


from


LoansAndSavings


where


([LoansIndicatorDate].[All LoanIndicators].[2004].[Quarter
4].[November].[30])


My Data is like :


ProductType Account 1D IndicatorDate Arrear1to8
1 01 11-2-2004 80
1 01 11-15-2004 50
1 01 11-28-2004 100
1 02 11-13-2004 50
2 03 11-2-2004 30


For a Single ProductType "1" i have 2 acccountID 01,02


for Account ID "01" Previous existing Date is
IndicatorDate 11-28-2005 . Arrear Amount is 50

For Account ID "02" Previous existing Date is IndicatorDate 11-2-2005 .

Arrear Amount is 100


I want their sum at the ProductType level i.e . 150 and not just 50.
Query gives correct results for each account but its not summing the
accounts Arrear Values :


I get the following result:


ProductType Arrear1to8
1 50
2 30


For Product type 1 i should get 150 and not 50 as there are 2 accounts
under this ProductType.


Shuchi.

Please someone help on this..