How can I transform the given formula ( CPP enhancement) into a calculation in the Prophix DPM module Personal cubes ? Any suggestions would be greatly appreciated. The formula is as follows:
=IF([ANNUALIZED PAY]<68500, 0, IF(AND([ANNUALIZED PAY]>=68500, [ANNUALIZED PAY]<=73200), ([ANNUALIZED PAY]-68500)*0.04, IF([ANNUALIZED PAY]>73200, (73200-68500)*0.04, 0)))
I have manage the CPP enhancement Formula into a calculation in the Prophix DPM module, I need create separte calculation with
Conditon 1 <68500 ,
Condition 2 >=68500 and <=73200 ,
Condion 3 >73200
then use calculation TAB for
Condition 1 : [ANNUALIZED] PAY *0
Condition 2: [ANNUALIZED PAY]-68500)*0.04,
Condtion 3: (73200-68500)*0.04
Hi Sammil,
It looks like you are using an IF calculation in the example you posted above. We have a built in TAX calculation in the DPM that will allow you to set a tax rate (1), the maximum applicable value (2) and the exemption value (3).
Can you try this approach and see if that gets you what you want?
Hi @sammil.hossain,
You have a nested IF statement. For something like this, one approach is to have multiple calculations in DPM (using a combination of IF and Formula calculation types), to get to your desired result. This is a very common use case in DPM that I have seen in multiple clients. Though you would need to create multiple (possibly 3-4 calculations), it can be easy to manage coz it’s logical and distinct.
This would be a good question to pose to our consultants by scheduling a call using your CSP.
Hope this helps,
Navin
I am unfamiliar with DPM, but these calculations could be helpful for stat accounts in cubes.
I am unfamiliar with DPM, but these will be very helpful in the specific accounts in cubes
As above, if you simplify your formula into it’s basic calculations then you can use the calculation function with conditions to work out the values that the conditions apply to, anything between 68500 and 73200 should have the formula *.04 applied.
Nested ifs formula would work on this
Mirals’ post was insightful. That was helpful.
Very helpful information.
Sorry I don’t have any helpful information.
@Julie, yes, you are correct. We employ the calculation function with conditions. We have created three different calculations with conditions, and it works fine.
Appreciate the insight from everyone.
I feel the easier approach would creating 3 Calculation Variables:
Band1: Pay < 68500 than Result as 0
Band2: Pay >= 68500 and <=73200 than (Pay - 68500)0.04
Band3: Pay > 73200 than 47000.04
CPP = Band1 + Band2 + Band3
It will be easier to manage the bands in future.
@ali.arsalan.1, I think that’s the way I would tend to approach it too. Although multiple IFs get us there, it really is a BAND concept, and I find that in DPM using the method the conveys the idea is very helpful down the road when, months later, making changes. (“Now, just exactly WHAT was I thinking when I did this?”)
I am unfamiliar with DPM, but these will be very helpful
I fully agree; we have created it in the same way as per your examples, with three calculations using three different functions. Thank you.
This concept is interesting to me and easily relatable to our company. Thank you for proving this insight 
Thank you for proving this insight everyone!
