Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rewrite IF function

I am using this formular, in order to evaluate if some requirement are fulfil. But it does not give me "No" for B10, but works with B12 for both "Yes" and "No".    Dag = VAR dag = Ark1[øvelsesnu...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    Please update the formula of calculated column [Dag] as below and check whether you can get the correct result.

     

    Dag =
VAR dag = Ark1[øvelsesnummer]
RETURN
    IF (
        (
            Ark1[Station] = "Christianshavn"
                && Ark1[Dato] <= DATE ( 2020, 10, 17 )
                && dag IN { "B10", "B12" }
        )
            || ( dag
            IN { "B1", "B2", "B10", "B12" }
            && Ark1[Station] = "Frederiksberg"
            && [Week Nr] <= 17 ),
        "Ja",
        "Nej"
    )

     

    If the above one can't get the correct result, please provide some sample data(exclude sensitive data) and the related calculation logic in order to provide the suitable solution for your scenario. Thank you.

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

It did not work. The formula has to end with a no, as no is given for both blank cells but also if the criteria in are not fulfill in any of the iterations. 

Dag =
VAR dag = Ark1[øvelsesnummer]

return
SWITCH(TRUE(),
ISBLANK(Ark1[Dato]),"Nej",
Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Ja",
Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Ja",
dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Ja",
dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Fælledvej" && [Week Nr] <= 4, "Ja", "Nej"
 
It works perfectly for B12. But for B1, B2 and B10 it gives me incorrect values for yes and no due to the provided criteria. 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

Please update the formula of calculated column [Dag] as below and check whether you can get the correct result.

 

Dag =
VAR dag = Ark1[øvelsesnummer]
RETURN
    IF (
        (
            Ark1[Station] = "Christianshavn"
                && Ark1[Dato] <= DATE ( 2020, 10, 17 )
                && dag IN { "B10", "B12" }
        )
            || ( dag
            IN { "B1", "B2", "B10", "B12" }
            && Ark1[Station] = "Frederiksberg"
            && [Week Nr] <= 17 ),
        "Ja",
        "Nej"
    )

 

If the above one can't get the correct result, please provide some sample data(exclude sensitive data) and the related calculation logic in order to provide the suitable solution for your scenario. Thank you.

Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    It helped! Thank you for your help!