Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Ignore User Selection and show count of a certain date range

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!

5 Replies
shraddha_g
Honored Contributor III

Re: Ignore User Selection and show count of a certain date range

what is format of date in IncidentDate Field?

Vegar
Valued Contributor

Re: Ignore User Selection and show count of a certain date range

Dates can be tricky in set hence they are dual() values. Try to get your set intervall to work, you might need to put ' around your dates as it is the text value.

Not applicable

Re: Ignore User Selection and show count of a certain date range

It is MM/DD/YYYY.

Thanks.

Vegar
Valued Contributor

Re: Ignore User Selection and show count of a certain date range

I issue like this is always easier to solve by the community if you provide som kind of sample data, but I'll give it a try.

Try

Count({1<IncidentDate={"$(='<=' & Date('07/27/2016') & '>=' & date('10/31/2016')) "},StateValue={'Resolved'}>}DISTINCT IncidentID)

The set {1} will ignore all your selection and you don't need to explicit clear other fields.

If you are selecting ModifiedDates my guess is that your final expression will look something like this

Count({1<IncidentDate={"$(='<=' & Date(max(ModifiedDate)) & '>=' & date( addmonths(max(ModifiedDate), -3 ) ) "},StateValue={'Resolved'}>}DISTINCT IncidentID)

This will ignore all selections, but select IncidentDate within three months from your largest ModifiedDate-selection where the stateValue is Resolved.

MVP
MVP

Re: Ignore User Selection and show count of a certain date range

Except for what vegar.lie.arntsen‌ has pointed out that you expression probably don't need individual ignoring of your date fields because you are ignoring them all using a 1, I don't see anything wrong with your expression from what you have shared. Would you be able to share a sample where you can show the issue and also point out what are you expecting to see as a sample?