topic Re: Max # of Days Open if conditions met in QlikView

Max # of Days Open if conditions met

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Max # of Days Open if conditions met

sunny_talwar — Mon, 19 Jun 2017 18:40:18 GMT

Not entirely sure, but may be this

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

Re: Max # of Days Open if conditions met

lironbaram — Mon, 19 Jun 2017 18:47:45 GMT

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))

Re: Max # of Days Open if conditions met

Mon, 19 Jun 2017 18:59:50 GMT

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

Re: Max # of Days Open if conditions met

Mon, 19 Jun 2017 19:00:45 GMT

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.