Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
what is format of date in IncidentDate Field?
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.
It is MM/DD/YYYY.
Thanks.
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.
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?