topic Id Exists during date range in QlikView

Id Exists during date range

craig157 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Having trouble on this one but it may just be the way I'm phrasing this so hopefully it makes sense.

I have three fields - StartDate, EndDate & DateRange (please note EndDate will be null for lots of data)

Id	StartDate	EndDate	DateRange
A1	15/07/2018	29/09/2018	15/07/2018-29/09/2018
A2	04/01/2018	Null	04/01/2018-
A3	12/12/2017	01/09/2018	12/12/2017-12/10/2018
A4	05/04/2018	Null	05/04/2018-

I've got listbox filters I use on some of the charts to narrow down the data.

But problem is, I need to have a count of active records during a certain period.

So if I select 'July 2018' my result should be 4, Select 'Jan 2017' should be 0, Select 'Dec 2017' should be 1, Select 'Today' should be 2

How would I achieve the count?

Thanks

Re: Id Exists during date range

swuehl — Fri, 12 Oct 2018 10:41:02 GMT

Create reference dates for your intervals (you can set the EndDate without a value for example to Today(1) in the script, or any other appropriate date):

Creating Reference Dates for Intervals

Re: Id Exists during date range

craig157 — Thu, 08 Nov 2018 13:55:40 GMT

I've ended up setting a Stored Procedure in the SQL table and just executing that to Qlik.

Just using the below:

AssetCount:

sql exec ('[dbo].[usp_InScopeMonth]');

Only downfall is now my dates don't relate to the Original Date Selector as I didn't want to create any loops in the Script.