Calculations based on text
Hi,
I have an issue regarding how to set up a formula to get a calculation to work. I have a mapping module, where you can enter how a specific calculation should be done, looking like below:
Driver  Component_1  Calc_1  Component_2  Calc_2  Component_3 
Revenues  # units sold  *  average price per unit  BLANK  BLANK 
The format of the line items component_1, 2 3 as well as calc_1 and 2 are list. One list containing the drivers and another containing the different calculation methods (+, , / and *). The dimension of the module is the same as for the component line items, containing the drivers.
Let's assume component_1=10 and component_2=10. I then have another module, where I collect the values for the different components, looking like the following:
Driver  Component_1  Calc_1  Component_2  Calc_2  Component_3  RESULT 
Revenues  10  *  20  BLANK  BLANK  ERROR 
I now have the values and the calculation method to use, but the problem is the formula for "RESULT". I obviously want it to be dynamic, so that if I would have  instead of *, it would work automatically, all depending on the first module and what you have chosen there. Does anyone know what this formula would look like?
Also, if I were to have a component_3=5 and perhaps have a mix of multiplication and addition, for example have a module 2 looking like the following:
Driver  Component_1  Calc_1  Component_2  Calc_2  Component_3  RESULT 
XX  10  +  20  *  5  ERROR 
Then I obviously want the formula to understand that * comes before +, so that the result is 5*20 + 10 = 110 and NOT 10+20 * 5 = 150. What would this formula look like?
Any help or guidance is highly appreciated, thanks a lot in advance!
Best Answer

Hi,
I do not believe it is possible to achieve the outcome you require using your current approach. This is because Anaplan does not have the functionality to format a line item with a mathematical operator such as , +, x or /
One way that this could be possible is to create a list to hold you operators ( , +, x or / ). Format the line items above with this list. However, you will need to create an option for each permutations ( 5^2 = 25 ) The result formula will then need to cycle through each in order to find the correct combination. The formula would consist of a series of nested IF..THEN..ELSE functions each one tested a single combination.It would be possible to reduce the size of the IF..THEN..ELSE statement you could hold the calculations in a systems module and pick the relevant line item based in the combination of the two options.
However, this will create a number of empty cells or sparsity as it is most likely you will only ever be using a fraction of the options available. Heavily nested IF statements will lower model performance if they are too long.
Therefore, I would question why you need this functionality!
I would recommend that you plan and define your Measures and KPIs individually and from the outset.
I realise this is not the answer you sought but what you are trying to do is not possible.3
Comments

0

Hi Chris,
Thanks a lot for your reply. It works with a formula like the following:
+
IF '2' = Calculation list.'+' AND '4' = Calculation list.'+' THEN '1' + '3' + '5' ELSE
IF '2' = Calculation list.'+' AND '4' = Calculation list.'' THEN '1' + '3'  '5' ELSE
IF '2' = Calculation list.'+' AND '4' = Calculation list.'*' THEN '1' + '3' * '5' ELSE
IF '2' = Calculation list.'+' AND '4' = Calculation list.'/' THEN '1' + '3' / '5' ELSE
IF '2' = Calculation list.'' AND '4' = Calculation list.'+' THEN '1'  '3' + '5' ELSE
IF '2' = Calculation list.'' AND '4' = Calculation list.'' THEN '1'  '3'  '5' ELSE
IF '2' = Calculation list.'' AND '4' = Calculation list.'*' THEN '1'  '3' * '5' ELSE
IF '2' = Calculation list.'' AND '4' = Calculation list.'/' THEN '1'  '3' / '5' ELSE*
IF '2' = Calculation list.'*' AND '4' = Calculation list.'+' THEN '1' * '3' + '5' ELSE
IF '2' = Calculation list.'*' AND '4' = Calculation list.'' THEN '1' * '3'  '5' ELSE
IF '2' = Calculation list.'*' AND '4' = Calculation list.'*' THEN '1' * '3' * '5' ELSE
IF '2' = Calculation list.'*' AND '4' = Calculation list.'/' THEN '1' * '3' / '5' ELSE/
IF '2' = Calculation list.'/' AND '4' = Calculation list.'+' THEN '1' / '3' + '5' ELSE
IF '2' = Calculation list.'/' AND '4' = Calculation list.'' THEN '1' / '3'  '5' ELSE
IF '2' = Calculation list.'/' AND '4' = Calculation list.'*' THEN '1' / '3' * '5' ELSE
IF '2' = Calculation list.'/' AND '4' = Calculation list.'/' THEN '1' / '3' / '5' ELSE0
However, I do agree with you that this will reduce the performance of the model, and I'll look into alternative ways of doing this. But once again, thanks a lot!
0