Hi, I have a dimension with a few percentages (100%, 50%, 25%, 0%) as recovery. Their element type are all simple.
I'm trying to get a calculation based on the said percentages but don't know how to refer to it on the right side. Right now I'm ruling it one by one such as:
['Recovery', '75%'] = N: ['Total Expenditure'] * 0.75;
I think there should be a way to rule it so that I don't have to rule every percentage, but don't know how.
Appreciate if someone can point out how to deal with it.
Thanks.
Kenneth
Rule to calculate based on dimension item (percentage)
- Steve Rowe
- Site Admin
- Posts: 2415
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Rule to calculate based on dimension item (percentage)
Lets say your '75%' are in a the dimension Percentage
Then
['Recovery', '75%'] = N: ['Total Expenditure'] * Numbr(!Percentage);
this ought to simplify to
['Recovery'] = N: ['Total Expenditure'] * Numbr(!Percentage);
Alternatively (and I probably would) put a numeric attribute on the dimension
['Recovery'] = N: ['Total Expenditure'] * Attrn('Percentage , !Percentage , 'Percentage To Apply');
The above is (probably) not a great design. If want you want to do is really as simple as the above then consider creating an element called 'Recovery at 75%' and make Recovery a child of it with a weighting of 0.75. This will be more efficient calculation wise and you don't need another dimension to do it in.
Then
['Recovery', '75%'] = N: ['Total Expenditure'] * Numbr(!Percentage);
this ought to simplify to
['Recovery'] = N: ['Total Expenditure'] * Numbr(!Percentage);
Alternatively (and I probably would) put a numeric attribute on the dimension
['Recovery'] = N: ['Total Expenditure'] * Attrn('Percentage , !Percentage , 'Percentage To Apply');
The above is (probably) not a great design. If want you want to do is really as simple as the above then consider creating an element called 'Recovery at 75%' and make Recovery a child of it with a weighting of 0.75. This will be more efficient calculation wise and you don't need another dimension to do it in.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
Re: Rule to calculate based on dimension item (percentage)
I tried NUMBR and for some reason it didn't work for me. I believe I used the wrong bracket.
I agree that using attribute is not the best idea, thought of it then dropped it.
So it's working now, thanks!
I agree that using attribute is not the best idea, thought of it then dropped it.
So it's working now, thanks!