IF CONDITIONAL FORMULAE

PrevContributor
PrevContributor
in Modeling

Dear Anaplanners: I have a question regarding usage of IF conditional statement. All the conditional constructs in Anaplan are related to IF with ELSE as a mandatory clause. However, I have following requirement -  1. I need to chnage the contents of cell A only when condition_A is satisfied. Otherwise, I do not want the contents to be changed at all.  Above requirement can easily be satisfied if I have something like IF CONDITION_A THEN <> for the formula of cell A. But, I cant use IF without having ELSE.  So, I tried - IF CONDITION_A THEN <> ELSE CELL_A for the forumla of cell A. But in this case, it is a circular reference and formula gets errored out.  I am sure this is very common requirement, so not sure what I am missing...... Thanks! - Akshay Oak.

Tagged:

Best Answer

  • cecilia_peterss
    cecilia_peterss
    Answer ✓
    Hi Akshay,

    You might have solved this already since the post is oldish, but have you tried to set 'Edit From' and 'Edit To' in Settings / Versions to 'Jan 15' for Actual? This way you prevent the users from changing the previously entered value.

    /Cecilia

Answers

  • greg_kajewski
    greg_kajewski
    Hello Akshay, 

    Would it be possible to use two line items in order to get to your desired result. It appears to me that you would need to be able to enter data under the else clause. Is that correct? If so, you could try the following option: 

    Line Item Stage:   Used to calculate your Else Clause, it would have no formula but has a place for input of necessary information.

    Line Item:   IF CONDITION_A THEN <> ELSE Line Item Stage

    I know this appears to be similar to your solution of  "IF CONDITION_A THEN <> ELSE CELL_A", but i believe that if the line item is only used to hold the data relevant to your else statement, no circular reference would occur. 
  • PrevContributor
    PrevContributor
    Hello Greg:

    Thanks for the reply. Not sure I understand but let me clarify.

    1. by Line Item Stage - do you mean some intermediate line item? 

    2. I tried something like this. For example, I have a line item A, I caluclate thie item based on certain rules and calculation is okay. Now, I copy the same number in intermediate line item, for example line_intermediate_A. And then write IF formulas as - 

    IF <> DO SOMETHING ELSE line_internediate_A. 

    However, even in this case I get circular reference error. 

    I do not need to enter data under else clause, I need to retain the pre-existing data under the else clause.


    Thanks! - Akshay Oak.
  • elizabeth_schaf
    elizabeth_schaf
    Hello. Were you able to resolve this?
    You would need 2 line items to accomplish what you are requesting.
    The first line item would complete the calculation. The second line item would contain your IF statement which after the ELSE would refer to the calculation line item.
  • PrevContributor
    PrevContributor
    Hello Elizabeth:

    Thanks for trying to help me here but I am still struggluing to get through this. I will give explicit details of use-case: 

    Lets consider following setup (please refer initial setup - 1.png attached)

    Now my use case is as follows - 

    1. 2 represents number of active month. So, in my example February is active month.
    2. Now I enter order quantity as shown in the figure.
    3. If I enter value in the month of March, April or June etc. it should result in 0. This is easy to achieve and as you can see even if there is a value in the month of May 15 for "Enter Order Quantity", I still see 0 in Order Quantity. This is as I desire. 
    4. Now, important part is I do not want Order Quantity to be changed in the past month, for example - January in this case. So, even when I change from 1000 to 300 for Jan 15. It should still stay 1000. So, one way  was -  

    IF MONTH(PERIOD(END())) = Active Month THEN Enter Order Quantity ELSE IF MONTH(PERIOD(END())) > Active Month THEN 0 ELSE  Order Quantity.

    Here I was hoping that only when I am in the active month I want Enter Order Quantity to be copied to Order Quantity if I am in a month less than active month (i.e. Jan) I want to retain whatever value was there before and if I am in a month greater than active month then I want to have value of 0. But above formula results in circular refernce. 

    So, can you help me in achieving above result?

    Thanks! - Akshay Oak.

Categories