5 Replies Latest reply: Nov 1, 2016 9:54 PM by Sunny Talwar RSS

    Ignore User Selection and show count of a certain date range

    Reiko Hayami

      I have Incidents that have StateValue that are Active, Resolved, or Cancel.

      There are 2 date dimensions, 1 is IncidentDate when an incident is created, 1 is ModifiedDate when an incident is resolved.

       

      I would like to count the # of Resolved IncidentIDs that were created (IncidentDate) between the dates of 07/27/2016 and 10/31/2016 (going back about 3 months) regardless of IncidentYear, IncidentMonth, IncidentWeek, and IncidentDate selection by a user.

       

      For example, I need to get a count of Resolved IncidentIDs in Oct-16.  If I just do Count({< [StateValue] = {'Resolved'} >} [StateValue]), when a user selects Oct-16, it will only show # of Resolved IncidentIDs that were created in Oct-16.  I need the # of IncidentIDs that were created between 07/27/2016 and 10/31/2016 and Resolved in Oct-16.  Is this even possible?  Everything I've tried failed.

       

      Count({1<IncidentMonth_Short=,IncidentYear=,IncidentWeek_Short=,IncidentDate=,IncidentDate={">=07/27/2016<=10/31/2016"},StateValue={'Resolved'}>}DISTINCT IncidentID)

       

      I have all this done out of a report in Excel, but of course the management wants to see it done in Qlik....

       

      Any help would be appreciated.

       

      Thank you!