IF function without ELSE condition

I am looking at a calculation where I want to have C = IF A > 0 THEN B ELSE DO NOTHING...

 

DO NOTHING needs to retain previous value on C.

Answers

  • HI @jairamkamath,

     

    We cannot write a formula without else condition in the IF statement. I would suggest, creating another line item says IF A>0 then B else C. If you want to take the previous period's value you can also say IF A>0 then B else PREVIOUS(C).

    I hope it helps!

     

  • Hi @jairamkamath ,

     

    As @kavinkumar Suggested that you can use previous function to get previous value of C. Please find the below screenshot.

     

    naushad786_0-1584511247855.png

     

    Thanks,

    Naushad

     

     

  • Hi @jairamkamath,

     

    This is possible using Previous function.

    Previous Function Takes the Previous value of the same line item.

    But your module should have Time as a dimension.

     

     

     

    Capture5.JPG

  • Are you looking to return the previous value of C as it relates to 'time' or as it relates to 'version'? 

     

    Previous time period = IF A >0 THEN B ELSE PREVIOUS(C)

     

    Previous version = IF A > 0 THEN B ELSE PREVIOUSVERSION(C)

  • Hi All,

     

    Thank you for the quick responses. It is my bad I did not frame the question correct. I was working with boolean in my case.

     

    Formula: C = IF A then PREVIOUS(C) ELSE B

     

    Example: 

    A=FALSE
    B=100

    Then  -> C = 100

     

    When A = TRUE
    2nd Run: C value should remain as 100.
    But C is getting ZERO'd out.

     

     

  • Hi @jairamkamath 

     

    I have tried to replicate your logic, i came up with few scenarios,let's say 

    Scenario 1.  your first case A= false, B=100  then C should be 100, this working perfectly.

     

    Screenshot_153.png

     

    Scenario 2: Here my month started from Jan 20 so we don't have any previous value for C then in this case when A= True , B= 100 then all C = 0. 

    I think you may have encounter with this case.

    Screenshot_154.png

     

    Scenario 3: here A= True for Jan but false for Feb , B=100 then you can get the value for C except Jan 20

     Screenshot_155.png

     

    Hope this helps!

    Thanks

    Akhtar

  • Hi All,

     

    When I mean Previous, I meant Previous value in the same cell. (Not the previous period value).

    Please check the below flow of data:

    Scenario-1 Jan-20Feb-20Mar-20Apr-20
    AFALSEFALSEFALSEFALSE
    B100100100100
    C100100100100
         

     

    Now setting A to TRUE:

    Scenario-2 Jan-20Feb-20Mar-20Apr-20
    ATRUEFALSEFALSEFALSE
    B100100100100
    C100100100100

     

    Now changing Values in B. The C should retain 100 (previous value in same cell). - Ignore the calculated value.

    Scenario-3 Jan-20Feb-20Mar-20Apr-20
    ATRUEFALSEFALSEFALSE
    B200200200200
    C100200200200

     

     

    Thank you in advance!

     

    Regards,

    Jairam

  • @jairamkamath,

    Lets not over complicate things! Why don't you create another line item D and say IF A then B else C as I suggested in my earlier post? Do you have any size constrain?

    You cannot write a formula to retrieve the previous value in a cell.

     

  • Hi @jairamkamath,

     

    As you mentioned Logic earlier: IF A then Previous(C) else B

     

    and considering your scenario 3

     

    Scenario-3 Jan-20Feb-20Mar-20Apr-20
    ATRUEFALSEFALSEFALSE
    B200200200200
    C100200200200

     

     

    Your Previous question : "Now changing Values in B. The C should retain 100 (previous value in same cell). - Ignore the calculated value."

     

     

    and i didn't understand how and why C should retain 100.

     

    can you explain in detail....?

     

     

  • Hi @prabhu 

     

    A works like lock on C. If A is TRUE, C should no longer be calculated. It should be considered as frozen period.

  • Hi @jairamkamath,

     

    In this Scenario, What should be the output (C) ...?

     

     

    Scenario-4Jan-20Feb-20Mar-20Apr-20
    ATRUEFALSEFALSETRUE
    B78345693
    C    

     

    I am little confused, in the previous post you have mentioned, (get the previous value in same cell not previous period).  In this Scenario, what is the previous value in same cell for the Period  - Apr-20 ?

     

  • @prabhu The Previous same value for Apr-20 would be whatever value that was in Apr-20 before A became TRUE. In this case it should remain blank.

     

  • Hello @jairam,
    As @kavinkumar mentioned it, there is no way to retrieve the previous value of a "cell".
    Can you provide a bit more context for us to understand what you are trying to achieve?
  • Hi @fabien.junod  @kavinkumar  -

     

    I am looking to lock the plan that is in line item C based on A boolean (lock). The periods that are to be locked can be random. Example: Store 1 may have locked April-20 while Store 2 may have locked only till Mar-20. line Item B is plan file upload which is also editable by user on the dashboard.

     

     

  • Hi @jairamkamath 

    Not sure if this will help but I would recommend using Dynamic Cell Access (DCA). It would allow you to "block" a cell and that way no one can modify it.

    https://help.anaplan.com/anapedia/Content/Modeling/Working_with_Data/DynamicCellAccess.htm

    In my example, I have used the following line items

    TRUE: Boolean set as "TRUE" (for the DCA)

    Lock (A): is the line item used to lock an input 

    Dynamic Cell Access - NOT A: is the line item used for the DCA. The formula is "NOT Lock(A)"

    B : is the stores plan. It can be uploaded from a file and also modified in the module/dashboard (if it's not locked)

    Anaplan Stores1.JPG

     And this is how you set-up the DCA

    -  The Write Access Driver is the line item Dynamic Cell Access - NOT A. Becasue the DCA works opposite to locking a cell. You need to tell select the cells that are OK to be modified/read

    - The Read Acces Driver is "TRUE", that way regardless of the write access, you can always view the number on that cell

    - Note: You need to make sure that the summary for the access driver line items is NOT "None"

    Anaplan Stores2.JPG

    Hope this helps.

  • Hey @jairam,
    Were you able to review the proposed solution. Did it help?
  • How are you defining scenarios? Are you using the native versioning as the scenario or did you create a "fake" scenario list? If using version, you can use the function of PREVIOUSVERSION() as mentioned in my other post.

     

    If you're using a "fake" scenario list, my suggestion is to create some sort of mapping module to tie each scenario list item to a previous version. You can then reference this module in your formula.

     

    Your first module will be dimensioned by the fake scenario list and 1 line item of “previous scenario.” Tie each scenario to what you want the previous to be.

    taylor_rogers_0-1585345787463.png

    taylor_rogers_0-1585346038200.png

     

     

    Your second module will need to be dimensioned by the scenario list and a few different line items for C. One line item will set C = B since this one possible solution to your formula. The other line item will set C = Previous C because this is the other option. Your final C line item will then do the logic to determine which of the other 2 C line items it will take based on the value of A.

     

    taylor_rogers_3-1585345880275.png

     

     

     

    taylor_rogers_2-1585345787480.png

     

    I hope this helps!

     

    - Taylor