Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

Need help correcting my Formula

Hello,   Ive got a formula that needs some work, there are a lot of IF's in   Status Schouw = IF(OR(Adressen[R.N.A.]="R40";(Adressen[R.N.A.]="R42"));"";     IF(Adressen[Civiel geplande datum]=BL...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    RvdHeijden,

    There is no issue with the last line when I make a test using sample data in my table. Could you please check that if you change the data type of Schouw datum field to Date? If this field is a text field, the above error will occur because you are comparing text values with date values in the following part. Also ensure that you have defined the data type of the Civiel geplande datum field to Date.

    Adressen[Schouw datum]<NOW()

    If the above step doesn't help, please share sample data of your table for us to test.

    Regards,
    Lydia

danextian's avatar
danextian
Icon for Super User rankSuper User
9 years ago

hi RvdHeijden,

 

Your formula seems fine but you could display it a in a neater way using Daxformatter.com

 

Status Schouw =
IF (
    OR ( Adressen[R.N.A.] = "R40"; ( Adressen[R.N.A.] = "R42" ) );
    "";
    IF (
        Adressen[Civiel geplande datum] = BLANK ();
        "Civiel nog niet gepland";
        IF (
            Adressen[schouwen begin] = BLANK ();
            "Schouw nog niet gepland";
            IF (
                OR (
                    Adressen[schouwen gesloten] = BLANK ();
                    ( Adressen[schouwen begin] <> BLANK () )
                );
                "Bulkplanning";
                IF (
                    OR (
                        Adressen[schouwen gesloten] = BLANK ();
                        ( Adressen[Schouw datum] < NOW () )
                    );
                    "Te laat";
                    IF (
                        Adressen[schouwen gesloten] <> BLANK ();
                        "Gesloten";
                        IF (
                            MID ( Adressen[schouwen begin]; 14; 18 ) <> "00:00";
                            "Vaste afspraak";
                            IF (
                                SUMX ( adressen; Adressen[Civiel geplande datum] - NOW () )
                                    < 21;
                                "Te Laat";
                                ""
                            )
                        )
                    )
                )
            )
        )
    )
)
  • RvdHeijden's avatar
    RvdHeijden
    Icon for Post Prodigy rankPost Prodigy
    9 years ago

    danextianAnonymous

    Ive changed a few date colums because 2  were indeed referring to a tekst value.

    They looked like dates but they were the outcome of a formula and the data type was set to 'tekst'.

     

    So problem solved....thanks :)