Using Date Fields as Date range in Set Analysis

Anonymous — Thu, 12 Jul 2018 21:00:06 GMT

I have gone through various posts on this board about using Set analysis with a date range and most of them are using variables however I would like to know if its possible to use field values as upper and lower limits of the date range. I've tried using various versions of the answers in those posts but it doesn't seem to work with using fields. If you feel there is a better way of doing this please advise.

Below is an example of data I'm working with and the output I would like to see.

Metric	Sales Order Queue	Date/time submitted	Identifier
Apr-2018	A	4/2/2018 9:10:36 AM	1741149012
Apr-2018	A	4/2/2018 10:06:48 AM	1741138735
Apr-2018	B	3/30/2018 6:49:03 PM	1737563038
Apr-2018	B	4/4/2018 3:48:11 PM	1748058621
Apr-2018	B	4/11/2018 3:29:00 PM	1762442199
Apr-2018	B	4/30/2018 5:37:45 PM	1797845555
May-2018	A	5/1/2018 9:57:27 AM	1802094809
May-2018	A	5/3/2018 5:13:02 PM	1806739606
May-2018	A	5/18/2018 5:28:51 PM	1836743080
May-2018	A	5/31/2018 9:58:09 AM	1862036446
May-2018	B	4/30/2018 3:46:57 PM	1797833866
May-2018	B	5/1/2018 8:07:20 AM	1802112377
May-2018	B	5/31/2018 8:04:22 PM	1862340234
Last Month	A	6/1/2018 10:20:02 AM	1865112078
Last Month	A	6/19/2018 2:39:41 PM	1903162643
Last Month	A	6/28/2018 8:44:00 AM	1920200817
Last Month	A	6/29/2018 11:05:18 AM	1925136622
Last Month	B	5/31/2018 8:04:22 PM	1862340234
Last Month	B	6/19/2018 11:26:01 AM	1903112241
Last Month	B	6/29/2018 8:02:42 PM	1925322904
Orders Not Processed	A	7/11/2018 7:50:13 AM	1949722589
Previous Business Day	A	7/9/2018 5:20:09 PM	1945297737
Previous Business Day	A	7/10/2018 8:38:23 AM	1945709165
Previous Business Day	B	7/10/2018 2:08:34 PM	1949192337
Previous Business Day	B	7/10/2018 5:51:00 PM	1949293209

Connected to:

(the number of months increase every month as data from new report is added)

Metric	DateRangeStart	DateRangeEnd
Last Month	6/1/2018 12:00:00 AM	6/30/2018 11:59:59 PM
Previous Business Day	7/10/2018 12:00:00 AM	7/10/2018 11:59:59 PM
Orders Not Processed	7/11/2018 12:00:00 AM	7/11/2018 11:59:59 PM
Apr-2018	4/1/2018 12:00:00 AM	4/30/2018 11:59:59 PM
May-2018	5/1/2018 12:00:00 AM	5/31/2018 11:59:59 PM

Chart Output:

Metric	Sales Order Queue	Orders carried over from previous	# of Orders Submitted
Apr-2018	A	0	2
Apr-2018	B	1	3
May-2018	A	0	4
May-2018	B	1	2
Last Month	A	0	4
Last Month	B	1	2
Orders Not Processed	A	0	1
Previous Business Day	A	1	1
Previous Business Day	B	0	2

I would like the "# of Orders Submitted" field to count the number of orders between date range "DateRangeStart" and "DateRangeEnd".

Similarly "Orders carried over from previous" should count the number of orders with "Date/time submitted" less than "DateRangeStart".

Something like below but this doesn't seem to work:

=count({$<[Date/time submitted]={">=[DateRangeStart]<=[DateRangeEnd]"}>} DISTINCT Identifier)

Any ideas? Thanks in advance.

EDIT:

Solved it by using the following:

=Count(DISTINCT if([Date/time submitted]>=DateRangeStart and [Date/time submitted]<=DateRangeEnd,Identifier))

Re: Using Date Fields as Date range in Set Analysis

Anonymous — Fri, 13 Jul 2018 18:19:04 GMT

Solved it by using the following:

=Count(DISTINCT if([Date/time submitted]>=DateRangeStart and [Date/time submitted]<=DateRangeEnd,Identifier))

topic Using Date Fields as Date range in Set Analysis in QlikView

Using Date Fields as Date range in Set Analysis

Re: Using Date Fields as Date range in Set Analysis