topic Re: Ignore User Selection and show count of a certain date range in App Development

Ignore User Selection and show count of a certain date range

Sat, 16 Nov 2024 10:17:22 GMT

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!

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

shraddha_g — Sat, 29 Oct 2016 07:37:25 GMT

what is format of date in IncidentDate Field?

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

Vegar — Sat, 29 Oct 2016 17:21:08 GMT

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.

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

Mon, 31 Oct 2016 15:50:43 GMT

It is MM/DD/YYYY.

Thanks.

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

Vegar — Tue, 01 Nov 2016 22:11:28 GMT

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.

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

sunny_talwar — Wed, 02 Nov 2016 01:54:05 GMT

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?