Getting Specific Date Periods in ERS
Print Friendly View
written: 12/10/2010
last modified: 03/22/2024

PREREQUISITES

ERS 3.3 or 3.4


INTRODUCTION

There are quite a few options for filtering on date built into the filtering of ERS. If you choose a date type such as Measure Date in the filter field, then there are a myriad of possibilities for filtering. Here are a few options that can be helpful.

Is Before, Is After, Is in the range

These three are fairly obvious and somewhat limited. You specify a literal date and time. ERS will return any data before, after, or between the date(s) specified. The downside to these options is that you type in literal dates that will never change. Therefore, these queries are used when you have a specific need for a very particular time period.

Predefined Non-Continuous Date Ranges/Time Spans

If you choose "is in the keyword range", you can choose from predefined non-continuous date ranges such as 2 months ago, last quarter, 10 months ago, last year, etc. These are all specific time periods that give you whole months, quarters, etc. Therefore, if today is Jan 10, 2010, then "2 Months Ago" would give you all the data from November 1, 2009 to November 30, 2009. It would not give you November 10th to December 10th since it always goes back to the time period and then gives you the whole period. Likewise, "x quarters ago" always gives you whole quarters (Jan 1 - Mar 31, Apr 1 - Jun 30, Jul 1 - Sep 30, and Oct 1 - Dec 31). This keyword range option is dynamic based on the current date.

Example:
These are often used in the cycling filters for comparison. For instance, compare the Cpk for May vs. June vs. July.



Continuous Date Ranges/Time Spans
To get continuous time spans, use the "is in the last" keyword. A continuous time span is one that is still continuing. For instance, if today is Jan 10, 2010, "is in the last 6 months" would get you all data from July 10, 2009 to Jan 10, 2010. If you ran it the next day, it would get you July 11 through January 11th. The time period is considered continuous since it doesn't start and end in the past like "2 months ago". Instead, it starts in the past and ends in the present. This option is also dynamic based on the current date.

This filter type allows you a lot more flexibility since you specify how many of a particular time period you want to query. The options are Minutes, Hours, Days, Weeks, Months, and Years. As an example, to query all data in the last six hours, just choose "is in the last", then type a 6 in the text field and choose Hours from the drop down list.

There is also an opposing option called "is not in the last". This will give you the opposite of the above. Therefore, "is not in the last 6 hours" will effectively give you everything older than 6 hours in the system.

Example:
This can be used for a global filter on a data group/report/dashboard. For instance, you set up the cycling filters by machine; Machine 1, Machine 2, Machine 3, etc. Then you only want to compare the machines for the last 2 months of data, so you set the global filter to "is in the last 2 months".

Continuous Date Ranges/Time Spans in the Past
If you use the two continuous query options above, you can actually create continuous time periods in the past. Consider the picture below. In this case, the query that is chosen can be interpreted as "show me all the data in the last 3 hours and not in the last 2 hours." Therefore, show me all the data from 2 hours ago. So if the current date/time is Jan 10, 2010 2:14pm, then this query would return all the data between 11:14am and 12:14pm on the same day (between 2 and 3 hours ago).

Example:
This can be used for all sorts of interesting queries. For instance, let's say you wanted to have a dashboard that showed meters for the number of parts measured by hour. You could set up 8 queries for the last 8 hours ("is in the last 9 hours" and "not in the last 8", "in the last 8" and "not in the last 7", "in the last 7" and "not in the last 6", etc), set up a data group using all of these queries, and then attach a dashboard radial gauge showing the counts. The result would be 8 speedometers showing the counts of parts measured by hour.

Time Spans

To filter by a particular time span regardless of date, use Measure Time as the filter field. Using Measure Time, filters on just the time portion of the measure date and ignores the date. Therefore, if you set it to Measure Time "is in the range" of 09:00 - 17:00, you will get all data made during that time period across days. Therefore, all the data from today between 9 and 5, yesterday between 9 and 5, the day before and so on.

Example:
This is typically used to compare data across shifts. For instance 9a-5p, 5p-1a,1a-9a.


CONCLUSION

These were just a few of the many options available with date filtering in ERS. As always, experiment to make sure you are getting the filter to work the way you intend.

Applies To

Enterprise Report Scheduler v.4.20
Enterprise Report Scheduler v.3.40
Enterprise Report Scheduler v.3.30