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:

DriverComponent_1Calc_1Component_2Calc_2Component_3
Revenues# units sold*average price per unitBLANKBLANK

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:

DriverComponent_1Calc_1Component_2Calc_2Component_3RESULT
Revenues10*20BLANKBLANKERROR

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:

DriverComponent_1Calc_1Component_2Calc_2Component_3RESULT
XX10+20*5ERROR

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. 

Answers

  • @ChrisAHeathcote 

     

    Succinct!! Nice One

  • 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' ELSE

    0

     

    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!