Hi,
I am trying to create a formula where I have 2 is blank conditions and 3 outcomes (terminology may not be right here).
I can get the formula to work if I only have 2 outcomes, but as soon as I add the 3rd I get the following message "Too many arguments to function 'IF'. Expected 3 but got 4." I am not sure how to rectify this.
This is what I am trying to do:
if(and(isBlank({Handover Commit Month}),isBlank({Projected Handover KPI})),dateadd({BD - TECH HO},182,"d"),{Projected Handover KPI},{Handover Commit Month})
I want to say if Handover Commit is blank then use Projected Handover KPI and if Projected Handover KPI is also blank then use BD - TECH HO + 182 days.
Hope this makes sense.
Emma B shared this idea · Jan 28, 2021
Comments
1 comment
Hi Emma,
Thank you for reaching out! When it comes to "if" statements, there would always need to be 3 arguments, and the general format would look as follows: if(Condition, What happens if the Condition is met, What happens otherwise). In this case, you would first need to start with a broader condition of both fields "Handover Commit Month" and "Projected Handover KPI" being blank. If this is true, we can tell the system to display "BD - TECH HO + 182 days", as you have in the beginning of your formula:
if(and(isBlank({Handover Commit Month}),isBlank({Projected Handover KPI})),dateadd({BD - TECH HO},182,"d"),"What happens otherwise")
We now need to specify what happens if the fields "Handover Commit" and "Handover KPI" are not both blank. Since you have mentioned you would like the "Projected Handover KPI" date to display if the "Handover Commit Month" field is blank, we would need a second "if" statement within the original one, to specify "what happens otherwise", similar to the following:
if(and(isBlank({Handover Commit Month}),isBlank({Projected Handover KPI})),dateadd({BD - TECH HO},182,"d"),
if(isblank({Handover Commit Month}), {Projected Handover KPI}, "What happens otherwise"))
Lastly, you would need to specify what happens if the "Handover Commit Month" field is not blank. For example, if it is not blank, meaning both fields "Handover Commit Month" and "Projected Handover KPI" are filled out, you could leave this calculated field blank, utilizing the null() function, or you could specify a different custom "what happens otherwise" condition here.
I am also linking a helpful document from our TrackVia Knowledge Base on some general formulas and their syntax below:
https://trackvia-uservoice-images.s3.amazonaws.com/exp_functions.pdf
If you have any additional questions, please do not hesitate to reach out to support@trackvia.com or call in to our Support line directly at 1 (800) 673 3302, we are happy to assist! Our Hours of Operation are Monday through Friday, 7:00 AM - 6:00 PM MST.
Kind Regards,
Mariya
Please sign in to leave a comment.