0

Logical syntax for multiple "if" choice

Hello everyone,

 

I'm trying to set up a logical formula that checks for two conditions instead of one, but I'm not sure how to do it. Let me explain:

Instead of writing "if X then Y else Z" I would like to write something like "if X, then if Y then Z else W, else A". If the first "if" is true, then it introduces a second choice to make. I'm just not sure about the correct syntax to use and the above doesn't work.

I'm completely new to this, so sorry if I'm missing anything obvious. I looked into the manual and previous forum submissions with no luck.

 

Many thanks,

Giovanni

4 replies

null
    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Giovanni

     

    This is what the manual says

     

    if Age < 18 then
    if Age < 13 then "Child" else "Teenager"
    else "Grown-up"
    end

     

    Regards John

    • Giovanni_Zagarella
    • 3 yrs ago
    • Reported - view

    Thank you for the support John. The formula now "works" (Ninox can't detect any issues with it), but in reality the field shows up as "no" instead of returning the intended value.

    My goal is to calculate the amount of days a certain project ran into a certain year. Let's say a project started in Sept 2018 and finished in Sept 2019, I want the formula to tell me it ran for X amount of days in 2019 (Jan to September 2019).

    This is what I came up with, it'd be great if someone could point me in the right direction to fix the code:

    if Projects.'Start date' > date(2019, 12, 31) then
    = "0"
    else
    if Projects.'End date' < date(2019, 1, 1) then
    = "0"
    else
    if Projects.'Start date' < date(2019, 1, 1) then
    if Projects.'End date' < date(2019, 12, 31) then
    = days(date(2019, 1, 1), Projects.'End date')
    else
    = days(date(2019, 1, 1), date(2019, 12, 31))
    end
    else
    if Projects.'Start date' > date(2019, 1, 1) and < date(2019, 12, 31) then
    if Projects.'End date' < date(2019, 12, 31) then
    = days(Projects.'Start date', Projects.'End date')
    else
    = days(Projects.'Start date', date(2019, 12, 31))
    end
    end
    end
    end
    end

    I'm guessing the problem is in the logical syntax, the single results of the condition work correctly - days(Projects.'Start date', date(2019, 12, 31)) as a standalone formula gives me the correct value, for example.

     

    Thanks in advance.

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Try this

     

    let a := date(2019, 1, 1);
    let b := date(2019, 12, 31);
    if 'Start date' = null or 'End date' = null or 'Start date' > b or 'End date' < a then
    0
    else
    let c := max('Start date', a);
    let d := min('End date', b);
    days(c, d) + 1
    end

     

    The first if checks that both date fields are filled in and they are within the range you are checking, the else take the maximum of your start date and 01/01/19 from the minimum of your end date and 31/12/2019 and adds 1 to it as the dates are inclusive.

     

    Regards John

    • Giovanni_Zagarella
    • 3 yrs ago
    • Reported - view

    Thanks so much John, this works perfectly - super appreciated. It will serve me as a model to write better formulas in the future as well!

     

    Best,

    Giovanni

Login to reply

Content aside

  • 3 yrs agoLast active
  • 4Replies
  • 1323Views