Calculations based on text

fredrickstraube
Frequent Contributor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Calculations based on text

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. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

3 REPLIES 3
ChrisHeathcote
Community Boss

Re: Calculations based on text

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. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

Misbah
Moderator

Re: Calculations based on text

@ChrisHeathcote 

 

Succinct!! Nice One

fredrickstraube
Frequent Contributor

Re: Calculations based on text

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!