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

VijayP's avatar
VijayP
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous 

SWITH(TRUE(),ISBLANK(Ark1[Dato]),"No",
Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Yes",
Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Yes",
dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Yes")
Try this 
in you formula you have already given now in case the value is blank and again mentioning the at the ending which i have removed and also converted to switch function for more readability
  • 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!