I have a cube showing customer debt where the fact table grain is based on customers. Each customer may have a debt value over the previous 4 years. Where a customer only has debt in the current financial year they are known as a "non hardened debtor". If they have debt over previous financial years they are known as a "hardened debtor". I have the following measures included in my fact table:
[measures].[current year debt]
[measures].[current year debt indicator]
[measures].[last year debt]
[measures].[last year debt indicator]
[measures].[previous year debt]
[measures].[previous year debt indicator]
[measures].[prior previous year debt]
[measures].[prior previous year debt indicator]
The debt measures are currency, the indicator is 0 if the customer has a 0.00 debt for that year and 1 if they have any other debt value.
I want to sum some of the debt figures based on whether the customer is a hardened debtor or not and have tried the following MDX function to produce the total debt for each hardened debtor:
To me this should sum the debt values for the customer if they have a debt indicator in any of the specified years, else return a 0, but when I look at the result at a higher level, the function works based on the sum of the debt indicators and doesn't take the result at the lowest level and aggregate it as I want it to.
How do I force the MDX to calculate at the lowest level and then aggregate the result based on slicer selection?
I can process the underlying fact table to create a new measure in the fact table, but I don't want to store unnecessary data (we have over 1,000,000 customers, approx 20 measures, and the table contains 24 months data).
[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]
[measures].[current year debt]
[measures].[current year debt indicator]
[measures].[last year debt]
[measures].[last year debt indicator]
[measures].[previous year debt]
[measures].[previous year debt indicator]
[measures].[prior previous year debt]
[measures].[prior previous year debt indicator]
The debt measures are currency, the indicator is 0 if the customer has a 0.00 debt for that year and 1 if they have any other debt value.
I want to sum some of the debt figures based on whether the customer is a hardened debtor or not and have tried the following MDX function to produce the total debt for each hardened debtor:
Code:
Iif([Measures].[Last Year Debt Indicator]+[Measures].[Previous Year Debt Indicator]+[Measures].[Prior Previous Year Debt Indic]>0, [Measures].[Current Year Debt] + [measures].[last year debt] + [measures].[previous year debt] + [measures].[prior previous year debt], 0)To me this should sum the debt values for the customer if they have a debt indicator in any of the specified years, else return a 0, but when I look at the result at a higher level, the function works based on the sum of the debt indicators and doesn't take the result at the lowest level and aggregate it as I want it to.
How do I force the MDX to calculate at the lowest level and then aggregate the result based on slicer selection?
I can process the underlying fact table to create a new measure in the fact table, but I don't want to store unnecessary data (we have over 1,000,000 customers, approx 20 measures, and the table contains 24 months data).
[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]