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!
1 -
Hi @jairamkamath ,
As @kavinkumar Suggested that you can use previous function to get previous value of C. Please find the below screenshot.
Thanks,
Naushad
1 -
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.
1 -
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)
1 -
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=100Then -> C = 100
When A = TRUE
2nd Run: C value should remain as 100.
But C is getting ZERO'd out.0 -
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
1 -
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
0 -
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.
1 -
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....?
0 -
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.
0 -
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 ?
0 -
@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.
0 -
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?0 -
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.
0 -
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)
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"
Hope this helps.
1 -
Hey @jairam,
Were you able to review the proposed solution. Did it help?0 -
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
1
