Nested IF Statements in Dimension Drill-Down

[pmenkveld]

Hi,

I am trying to visualize a count of different groups based on a nested if statement with a calculation in it. See below example:

=if([VDM OK]='OK', 'No investigation',
if(sum(([Maximum(VDM)] - FinanceAmount)<0) and sum(([MaximumLoan (VKM)] - LoanAmount) >=(-100)), 'Group 1',
if(sum(([Maximum(VDM)] - FinanceAmount) <(-100)) and sum(([MaximumLoan (VKM)]- LoanAmount) >=(-500)), 'Group 2',
if(sum(([Maximum(VDM)]- FinanceAmount)<(-500)), 'Group 3',
'Not Applicable')))

)
I want to count Group 1, Group 2 and Group 3. 

How can I create dimensions through this IF statement? Or is there an easier way to do this. The thing is, these groups do not exist in the database. It is an ad-hoc analysis. 

[basildur · Accepted solution]

You should probably create this in your Load Script, like:

if( [VDM OK]='OK', 'No investigation',
if( Sum([Maximum(VDM)] - [FinanceAmount])  <= 0  and Sum([MaximumLoan (VKM)] - [LoanAmount]) >= -100 , 'Group 1',
if( Sum([Maximum(VDM)] - [FinanceAmount]) < -100 and Sum([MaximumLoan (VKM)]- [LoanAmount]) >= -500 , 'Group 2',
if( Sum([Maximum(VDM)]- [FinanceAmount]) < -500 , 'Group 3',
'Not Applicable')))) as [YourFieldName]

And than use [YourFieldName] as a dimension on the front end.