Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

if statement with between conditions

Hi, Good Day.

Need help on my formula below. i am trying to create an if statement with between conditions but is getting error message.

 

DurationFilter =
If(INCIDENT_FACT[DURATION_HOURS] <=0.5, "Less Than 30 Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >0.5 And <=0.75, "Past 30 Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >0.75 And <1, "Past 45 Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >=1 And <1.5, "Past 1 Hr"),
    If(INCIDENT_FACT[DURATION_HOURS] >=1.5 And <=2, "Past 1Hr 30Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >2 And <2.5, "Past 2 Hr"),
    If(INCIDENT_FACT[DURATION_HOURS] >=2.5 And <=3, "Past 2Hr 30Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >3 And <3.5, "Past 3 Hr"),
    If(INCIDENT_FACT[DURATION_HOURS] >=3.5 And <=4, "Past 3Hr 30Mins"),
    If(INCIDENT_FACT[DURATION_HOURS] >4, "Past 3 Hr"),
If(BLANK())
 
Appreciate your help on this.
 
this is the error i am getting..
 

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    Test the below:

    DurationFilter =
IF (
    INCIDENT_FACT[DURATION_HOURS] <= 0.5,
    "Less Than 30 Mins",
    IF (
        INCIDENT_FACT[DURATION_HOURS] > 0.5
            && INCIDENT_FACT[DURATION_HOURS] <= 0.75,
        "Past 30 Mins",
        IF (
            INCIDENT_FACT[DURATION_HOURS] > 0.75
                && INCIDENT_FACT[DURATION_HOURS] < 1,
            "Past 45 Mins",
            IF (
                INCIDENT_FACT[DURATION_HOURS] >= 1
                    && INCIDENT_FACT[DURATION_HOURS] < 1.5,
                "Past 1 Hr",
                IF (
                    INCIDENT_FACT[DURATION_HOURS] >= 1.5
                        && INCIDENT_FACT[DURATION_HOURS] <= 2,
                    "Past 1Hr 30Mins",
                    IF (
                        INCIDENT_FACT[DURATION_HOURS] > 2
                            && INCIDENT_FACT[DURATION_HOURS] < 2.5,
                        "Past 2 Hr",
                        IF (
                            INCIDENT_FACT[DURATION_HOURS] >= 2.5
                                && INCIDENT_FACT[DURATION_HOURS] <= 3,
                            "Past 2Hr 30Mins",
                            IF (
                                INCIDENT_FACT[DURATION_HOURS] > 3
                                    && INCIDENT_FACT[DURATION_HOURS] < 3.5,
                                "Past 3 Hr",
                                IF (
                                    INCIDENT_FACT[DURATION_HOURS] >= 3.5
                                        && INCIDENT_FACT[DURATION_HOURS] <= 4,
                                    "Past 3Hr 30Mins",
                                    IF ( INCIDENT_FACT[DURATION_HOURS] > 4, "Past 3 Hr", BLANK () )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    OK, I can see what the problem is, however, the way to debug these things is to simplify the IF statement to something very basic that works: So, copy the code into notepad and remove almost everything except one IF test and the simplest of options.

    Let me know how you get on. 

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Test the below:

    DurationFilter =
IF (
    INCIDENT_FACT[DURATION_HOURS] <= 0.5,
    "Less Than 30 Mins",
    IF (
        INCIDENT_FACT[DURATION_HOURS] > 0.5
            && INCIDENT_FACT[DURATION_HOURS] <= 0.75,
        "Past 30 Mins",
        IF (
            INCIDENT_FACT[DURATION_HOURS] > 0.75
                && INCIDENT_FACT[DURATION_HOURS] < 1,
            "Past 45 Mins",
            IF (
                INCIDENT_FACT[DURATION_HOURS] >= 1
                    && INCIDENT_FACT[DURATION_HOURS] < 1.5,
                "Past 1 Hr",
                IF (
                    INCIDENT_FACT[DURATION_HOURS] >= 1.5
                        && INCIDENT_FACT[DURATION_HOURS] <= 2,
                    "Past 1Hr 30Mins",
                    IF (
                        INCIDENT_FACT[DURATION_HOURS] > 2
                            && INCIDENT_FACT[DURATION_HOURS] < 2.5,
                        "Past 2 Hr",
                        IF (
                            INCIDENT_FACT[DURATION_HOURS] >= 2.5
                                && INCIDENT_FACT[DURATION_HOURS] <= 3,
                            "Past 2Hr 30Mins",
                            IF (
                                INCIDENT_FACT[DURATION_HOURS] > 3
                                    && INCIDENT_FACT[DURATION_HOURS] < 3.5,
                                "Past 3 Hr",
                                IF (
                                    INCIDENT_FACT[DURATION_HOURS] >= 3.5
                                        && INCIDENT_FACT[DURATION_HOURS] <= 4,
                                    "Past 3Hr 30Mins",
                                    IF ( INCIDENT_FACT[DURATION_HOURS] > 4, "Past 3 Hr", BLANK () )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you.