Forum Discussion

JenniferHall's avatar
JenniferHall
Qrew Member
2 years ago

Building Nested Formulas

I am trying to assign a category ("On Time", "Past Due", "Exclude", and "Error") based on specific criteria (Installation Stage and dates). After which, I will create a pie chart showing the number of On Time Installations v. Past Due Installations.

Exclude
  • Return "Exclude" if the Installation stage equals "Refused", "Cancelled", "Remove from Scope", "Pending", and "3rd Party Pending"
Errors
  • Return "Error - No Target Date" if the Installation Stage equals "Installed" or "Planned" and the Target Date is blank
On Time v. Past Due
  • If Installation Stage equals Planned
    • Return "On Time" if Today() is less than or equal to than Target Date
    • Return "Past Due" if Today() is greater than (Target Date + 3 days)
  • If Installation Stage equals Installed
    • Return "On Time" if Actual Install Date is less than or equal to Target Date
    • Return "Past Due" if Actual Install date is greater than Target Date + 3 days)


------------------------------
Jennifer Hall
------------------------------
Formulas and functions
  • JenniferHall's avatar
    JenniferHall
    Qrew Member
    2 years ago
    This is how I currently have my formulas set up. However, I am receiving the error "Expecting boolean but found text" on the highlighted portion.

    //Exclude from Report
    If([Installation Stage]="Refused", "N/A",
    [Installation Stage]="Remove from Scope", "N/A",
    [Installation Stage]="Cancelled", "N/A",
    [Installation Stage]="Pending", "N/A",
    [Installation Stage]="3rd Party Pending", "N/A",

    //Errors
    If([Installation Stage]="Planned" or [Installation Stage]="Installed" and [Target Installation Date]=null, "Error - No Target Date",

    //On Time v. Past Due
    If([Installation Stage]="Planned" and Today()<=[Target Installation Date], "On Time",
    If([Installation Stage]="Planned" and Today()>([Target Installation Date]+Days(3))=true, "Past Due"),
    If([Installation Stage]="Installed" and [Actual Installation Date]<=[Target Installation Date], "On Time",
    If([Installation Stage]="Installed" and [Actual Installation Date]>([Target Installation Date]+Days(3)), "Past Due")
    )
    )
    )
    )
    )

    ------------------------------
    Jennifer Hall
    ------------------------------
    • KeithJusas's avatar
      KeithJusas
      Qrew Captain
      2 years ago
      don't think this is valid...
      If([Installation Stage]="Planned" and Today()>([Target InstallationDate]+Days(3))=true, "Past Due"),

      remove the "=true"

      ------------------------------
      Keith Jusas
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Commander
        2 years ago
        Keith found it.

        ------------------------------
        Don Larson
        ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Commander
      2 years ago
      Jennifer,

      Is your Formula Field a Formula Check Box instead of Formula Text?



      ------------------------------
      Don Larson
      ------------------------------
      • JenniferHall's avatar
        JenniferHall
        Qrew Member
        2 years ago
        The formula Field is Formula Text.

        ------------------------------
        Jennifer Hall
        ------------------------------