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[øvelsesnummer]

return
if(ISBLANK(Ark1[Dato]),"No",
if( Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B10", "Yes",
if( Ark1[Station] = "Christianshavn" && Ark1[Dato] <= DATE(2020,10,17) && dag = "B12", "Yes",
if( dag = "B1" || dag = "B2" || dag = "B10" || dag = "B12" && Ark1[Station] = "Frederiksberg" && [Week Nr] <= 17, "Yes", No"))))
 
I hope someone can help me figure this out or rewrite the formula 🙂 
 
Kind regards
Line
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

4 Replies

  • 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