Calculations based on text

fredrickstraube

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!

ChrisAHeathcote

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. 

Misbah

@ChrisAHeathcote 

 

Succinct!! Nice One

fredrickstraube

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!