Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Using Date Fields as Date range in Set Analysis

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.

MetricSales Order QueueDate/time submittedIdentifier
Apr-2018A4/2/2018 9:10:36 AM1741149012
Apr-2018A4/2/2018 10:06:48 AM1741138735
Apr-2018B3/30/2018 6:49:03 PM1737563038
Apr-2018B4/4/2018 3:48:11 PM1748058621
Apr-2018B4/11/2018 3:29:00 PM1762442199
Apr-2018B4/30/2018 5:37:45 PM1797845555
May-2018A5/1/2018 9:57:27 AM1802094809
May-2018A5/3/2018 5:13:02 PM1806739606
May-2018A5/18/2018 5:28:51 PM1836743080
May-2018A5/31/2018 9:58:09 AM1862036446
May-2018B4/30/2018 3:46:57 PM1797833866
May-2018B5/1/2018 8:07:20 AM1802112377
May-2018B5/31/2018 8:04:22 PM1862340234
Last MonthA6/1/2018 10:20:02 AM1865112078
Last MonthA6/19/2018 2:39:41 PM1903162643
Last MonthA6/28/2018 8:44:00 AM1920200817
Last MonthA6/29/2018 11:05:18 AM1925136622
Last MonthB5/31/2018 8:04:22 PM1862340234
Last MonthB6/19/2018 11:26:01 AM1903112241
Last MonthB6/29/2018 8:02:42 PM1925322904
Orders Not ProcessedA7/11/2018 7:50:13 AM1949722589
Previous Business DayA7/9/2018 5:20:09 PM1945297737
Previous Business DayA7/10/2018 8:38:23 AM1945709165
Previous Business DayB7/10/2018 2:08:34 PM1949192337
Previous Business DayB7/10/2018 5:51:00 PM1949293209

Connected to:

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

MetricDateRangeStartDateRangeEnd
Last Month6/1/2018 12:00:00 AM6/30/2018 11:59:59 PM
Previous Business Day7/10/2018 12:00:00 AM7/10/2018 11:59:59 PM
Orders Not Processed7/11/2018 12:00:00 AM7/11/2018 11:59:59 PM
Apr-20184/1/2018 12:00:00 AM4/30/2018 11:59:59 PM
May-20185/1/2018 12:00:00 AM5/31/2018 11:59:59 PM

Chart Output:

MetricSales Order QueueOrders carried over from previous# of Orders Submitted
Apr-2018A02
Apr-2018B13
May-2018A04
May-2018B12
Last MonthA04
Last MonthB12
Orders Not ProcessedA01
Previous Business DayA11
Previous Business DayB02

  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))

1 Solution

Accepted Solutions
Highlighted
Contributor
Contributor

Solved it by using the following:

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

View solution in original post

1 Reply
Highlighted
Contributor
Contributor

Solved it by using the following:

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

View solution in original post