4 Replies Latest reply: Jun 19, 2017 2:59 PM by Reiko Hayami RSS

    Max # of Days Open if conditions met

    Reiko Hayami

      We have CaseManagers, and each CaseManager has Incidents assigned to them.  Each Incident has IncidentID and IncidentDate, which is the date the Incident was opened.  We can get the # of days each Incident is open by Today()-IncidentDate

      For each CaseManager, I can get the maximum # of days an Incident is open by Max(Today()-IncidentDate)

      Now we want to get the max # of days open of the oldest Incident for each CaseManager only is the Incident has "Active" status and only if IncidentDate is in 2016 to 2017 without having any filters such as IncidentYear or StateValue.  I can count the # of Incidents successfully by the below.

       

      Working:

      Count({<StateValue={"Active"}, IncidentDate={">=01/01/2016<=12/31/2017"}>}IncidentID)

       

       

      I tried the below to get the Max # of days if these conditions are met:

      Not Working:

      IF({<StateValue={"Active"}, IncidentDate={">=01/01/2016<=12/31/2017"}>}Max(Today()-IncidentDate))

       

      Please tell me how I can get this to work.  Thanks!