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