IF function without ELSE condition

jairamkamath

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.

Accepted answers

All comments

kavinkumar

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!

naushad786

Hi @jairamkamath ,

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

Thanks,

Naushad

prabhu

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.

taylor_rogers

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)

jairamkamath

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.

Akhtar.shahbaz

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.

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.

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

Hope this helps!

Thanks

Akhtar

jairamkamath

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-20	Feb-20	Mar-20	Apr-20
A	FALSE	FALSE	FALSE	FALSE
B	100	100	100	100
C	100	100	100	100

Now setting A to TRUE:

Scenario-2	Jan-20	Feb-20	Mar-20	Apr-20
A	TRUE	FALSE	FALSE	FALSE
B	100	100	100	100
C	100	100	100	100

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

Scenario-3	Jan-20	Feb-20	Mar-20	Apr-20
A	TRUE	FALSE	FALSE	FALSE
B	200	200	200	200
C	100	200	200	200

Thank you in advance!

Regards,

Jairam

kavinkumar

@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.

prabhu

Hi @jairamkamath,

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

and considering your scenario 3

Scenario-3	Jan-20	Feb-20	Mar-20	Apr-20
A	TRUE	FALSE	FALSE	FALSE
B	200	200	200	200
C	100	200	200	200

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....?

jairamkamath

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.

prabhu

Hi @jairamkamath,

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

Scenario-4	Jan-20	Feb-20	Mar-20	Apr-20
A	TRUE	FALSE	FALSE	TRUE
B	78	34	56	93
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 ?

jairamkamath

@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.

fabien.junod

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?

jairamkamath

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.

fabien.junod

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.

fabien.junod

Hey @jairam,
Were you able to review the proposed solution. Did it help?

taylor_rogers

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.

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.

I hope this helps!

- Taylor

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385