topic Re: Calculations based on text in Anaplan Platform
https://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65081#M12092
<P>Hi,<BR />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 /<BR /><BR />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.</P><P> </P><P>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.<BR /><BR />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.<BR /><BR />Therefore, I would question why you need this functionality!<BR /><BR />I would recommend that you plan and define your Measures and KPIs individually and from the outset. <BR /><BR />I realise this is not the answer you sought but what you are trying to do is not possible. </P>Wed, 25 Mar 2020 16:40:58 GMTChrisHeathcote2020-03-25T16:40:58ZCalculations based on text
https://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65039#M12086
<P>Hi,</P><P>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:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="16.666666666666668%" height="30px"><STRONG>Driver</STRONG></TD><TD width="16.666666666666668%" height="30px"><STRONG>Component_1</STRONG></TD><TD width="16.666666666666668%" height="30px"><STRONG>Calc_1</STRONG></TD><TD width="16.666666666666668%" height="30px"><STRONG>Component_2</STRONG></TD><TD width="16.666666666666668%" height="30px"><STRONG>Calc_2</STRONG></TD><TD width="16.666666666666668%" height="30px"><STRONG>Component_3</STRONG></TD></TR><TR><TD width="16.666666666666668%" height="30px">Revenues</TD><TD width="16.666666666666668%" height="30px"># units sold</TD><TD width="16.666666666666668%" height="30px">*</TD><TD width="16.666666666666668%" height="30px">average price per unit</TD><TD width="16.666666666666668%" height="30px">BLANK</TD><TD width="16.666666666666668%" height="30px">BLANK</TD></TR></TBODY></TABLE><P>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.</P><P>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:</P><TABLE border="1" width="1279.0000000000002px"><TBODY><TR><TD width="210px" height="30px"><STRONG>Driver</STRONG></TD><TD width="211px" height="30px"><STRONG>Component_1</STRONG></TD><TD width="211px" height="30px"><STRONG>Calc_1</STRONG></TD><TD width="212px" height="30px"><STRONG>Component_2</STRONG></TD><TD width="211px" height="30px"><STRONG>Calc_2</STRONG></TD><TD width="118px" height="30px"><STRONG>Component_3</STRONG></TD><TD width="105px"><STRONG>RESULT</STRONG></TD></TR><TR><TD width="210px" height="30px">Revenues</TD><TD width="211px" height="30px">10</TD><TD width="211px" height="30px">*</TD><TD width="212px" height="30px">20</TD><TD width="211px" height="30px">BLANK</TD><TD width="118px" height="30px">BLANK</TD><TD width="105px"><FONT color="#FF0000">ERROR</FONT></TD></TR></TBODY></TABLE><P>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. <STRONG>Does anyone know what this formula would look like?</STRONG> </P><P>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:</P><TABLE border="1"><TBODY><TR><TD width="210px" height="30px"><STRONG>Driver</STRONG></TD><TD width="211px" height="30px"><STRONG>Component_1</STRONG></TD><TD width="211px" height="30px"><STRONG>Calc_1</STRONG></TD><TD width="212px" height="30px"><STRONG>Component_2</STRONG></TD><TD width="211px" height="30px"><STRONG>Calc_2</STRONG></TD><TD width="118px" height="30px"><STRONG>Component_3</STRONG></TD><TD width="105px"><STRONG>RESULT</STRONG></TD></TR><TR><TD width="210px" height="30px">XX</TD><TD width="211px" height="30px">10</TD><TD width="211px" height="30px">+</TD><TD width="212px" height="30px">20</TD><TD width="211px" height="30px">*</TD><TD width="118px" height="30px">5</TD><TD width="105px"><FONT color="#FF0000">ERROR</FONT></TD></TR></TBODY></TABLE><P>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. <STRONG>What would this formula look like?</STRONG></P><P>Any help or guidance is highly appreciated, thanks a lot in advance!</P>Wed, 25 Mar 2020 13:32:15 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65039#M12086fredrickstraube2020-03-25T13:32:15ZRe: Calculations based on text
https://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65081#M12092
<P>Hi,<BR />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 /<BR /><BR />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.</P><P> </P><P>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.<BR /><BR />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.<BR /><BR />Therefore, I would question why you need this functionality!<BR /><BR />I would recommend that you plan and define your Measures and KPIs individually and from the outset. <BR /><BR />I realise this is not the answer you sought but what you are trying to do is not possible. </P>Wed, 25 Mar 2020 16:40:58 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65081#M12092ChrisHeathcote2020-03-25T16:40:58ZRe: Calculations based on text
https://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65150#M12112
<P><LI-USER uid="19172"></LI-USER> </P><P> </P><P>Succinct!! Nice One</P>Thu, 26 Mar 2020 06:09:33 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65150#M12112Misbah2020-03-26T06:09:33ZRe: Calculations based on text
https://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65166#M12116
<P>Hi Chris,</P><P>Thanks a lot for your reply. It works with a formula like the following:</P><P><STRONG>+</STRONG><BR />IF '2' = Calculation list.'+' AND '4' = Calculation list.'+' THEN '1' + '3' + '5' ELSE<BR />IF '2' = Calculation list.'+' AND '4' = Calculation list.'-' THEN '1' + '3' - '5' ELSE<BR />IF '2' = Calculation list.'+' AND '4' = Calculation list.'*' THEN '1' + '3' * '5' ELSE<BR />IF '2' = Calculation list.'+' AND '4' = Calculation list.'/' THEN '1' + '3' / '5' ELSE</P><P><STRONG>-</STRONG><BR />IF '2' = Calculation list.'-' AND '4' = Calculation list.'+' THEN '1' - '3' + '5' ELSE<BR />IF '2' = Calculation list.'-' AND '4' = Calculation list.'-' THEN '1' - '3' - '5' ELSE<BR />IF '2' = Calculation list.'-' AND '4' = Calculation list.'*' THEN '1' - '3' * '5' ELSE<BR />IF '2' = Calculation list.'-' AND '4' = Calculation list.'/' THEN '1' - '3' / '5' ELSE</P><P><STRONG>*</STRONG><BR />IF '2' = Calculation list.'*' AND '4' = Calculation list.'+' THEN '1' * '3' + '5' ELSE<BR />IF '2' = Calculation list.'*' AND '4' = Calculation list.'-' THEN '1' * '3' - '5' ELSE<BR />IF '2' = Calculation list.'*' AND '4' = Calculation list.'*' THEN '1' * '3' * '5' ELSE<BR />IF '2' = Calculation list.'*' AND '4' = Calculation list.'/' THEN '1' * '3' / '5' ELSE</P><P><STRONG>/</STRONG><BR />IF '2' = Calculation list.'/' AND '4' = Calculation list.'+' THEN '1' / '3' + '5' ELSE<BR />IF '2' = Calculation list.'/' AND '4' = Calculation list.'-' THEN '1' / '3' - '5' ELSE<BR />IF '2' = Calculation list.'/' AND '4' = Calculation list.'*' THEN '1' / '3' * '5' ELSE<BR />IF '2' = Calculation list.'/' AND '4' = Calculation list.'/' THEN '1' / '3' / '5' ELSE</P><P>0</P><P> </P><P>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!</P>Thu, 26 Mar 2020 08:06:31 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Calculations-based-on-text/m-p/65166#M12116fredrickstraube2020-03-26T08:06:31Z