Max # of Days Open if conditions met

[#1]

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!

[sunny_talwar · Accepted solution]

Not entirely sure, but may be this

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

[lironbaram]

hi

assuming you want the result for each manger you can use this

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

[#4]

Thank you so much!!!!!! This worked perfectly.

[#5]

This one returned no value for all CaseManagers.  Might have been something in the pivot set up I already had.  But the previous one worked for me.  Thanks for your reply.