Summary

Helps to calculate values for intervals by transforming potentially unscaled interval (e.g. 08:22 – 17:34) into a series of scaled intervals (e., 08:30 – 09:00, 09:00 – 09:30 …)

 

Introduction

To decide whether such a scaled interval is considered to be a interval caused by the original interval is surprisingly tricky.
A nice analogy (by Wolfgang Aigner) is a mesh and the question whether each hole is filled or not.

This problem can be further refined by two aspects:

  1. When do short intervals fill larger holes E.g. if a customer waits from 8:02 to 8:05 …is this to be counted as a waiting customer in the interval 08:00 – 08:30?
  2. How many holes of the mesh are filled by a long interval E.g. how many 30' intervals are covered by the interval 08:17 – 17:14? Is 08:00 to 08:30 and 17:00 – 17:30 an interval? Are both considered, one …?

Example: Scaling of a table with raw time data

Situation

Raster the time interval 2007/01/01 6:00 to 2007/01/01 14:30 into 1-hour-intervals

Operation settingChoose date columns, scaling and calculation method (other examples see below).
Result
TIS ProjectConfluence Op Scaling.gzip

Want to learn more?

Time periods are set in a specific scale (minutes or hours).

Columns of input table

ParameterValueOpt.DescriptionExample
Identifier

System.Object

opt.Which columns should be evaluated for the identifier?-
Date + Time (from)

System.DateTime

-Column containing the start (date+time) of the time period.-
Date + Time (to)

System.DateTime

-Column containing the end (date+time) of the time period.-
Count

System.Object

opt.Column whose values are counted during scaling.-

Parameter

ParameterValueOpt.DescriptionExample
Scaling

System.Int32

  • 30 seconds
  • 1 minute
  • 1.5 minutes
  • 2 minutes
  • 5 minutes
  • 10 minutes
  • 15 minutes
  • 20 minutes
  • 30 minutes
  • 45 minutes
  • 1 hour
  • 1.5 hours (90 minutes)
  • 2 hours
  • 3 hours
  • 4 hours
  • 6 hours
  • 8 hours
  • 12 hours
  • 24 hours
  • Week
  • Month
  • Quarter
  • Year
  • Total period
-Input scaling, e.g. 10 minute scale.-
Calculation method

System.String

  • 100% in scale
  • 50% in scale
  • Start time in scale
  • End time in scale
  • Count start time only
  • Count final time only
  • Proportional time*value
  • Proportional value
  • Distribute value in pattern
  • Smallest value in the pattern
  • Largest value in pattern
-Example of calculation method '100% in scale': Scaling 1:00h, start day 0:00. Time period 6:05 to 8:10 occurs only in scaling 7:00 to 8:00.-
Convert 0 lines?

System.Boolean

-Prevents the output of rows which exclusively contain 0 values-
Cancel if more than 500,000 rows?

System.Boolean

-Cancel if more than 500,000 rows are generated?-

Example 1: Different calculation methods

The time interval 2007/01/01 6:00 to 2006/01/01 14:30 is applied to different calculation methods.

Calculation methodSettingsResult

See example above:

100% in a 1-hour raster

if Convert 0 lines? is not selected


100% in a 1-hour raster

if Convert 0 lines? is selected

50% in a 1-hour raster

no matter if Convert 0 lines? is selected or not

 

Example 2: Proportional time*value <> proportional time

The following time interval, value = 27 is rastered with different scaling methods.

Calculation methodSettingsResult
Proportional time*value

Explanation: Scaling interval = 2 minutes (120 seconds) determines the resulting proportion

row 1: half of the interval met, therefore 60/120*27=13.5

rows 2 and 3: interval fully met, therefore 120/120*27=27

row 4: half of the interval met, therefore 60/120*27=13.5

Proportional time

Explanation: Duration of the period in the whole data set (6 minutes = 360 seconds) determines the resulting proportion

row 1: 16:13 – 16:14 = 60 seconds, therefore 60/360*27= 4.5

row 2: 16:14 – 16:16 = 120 seconds, therefore 120/360*27= 9

row 3: 16:16 – 16:18 = 120 seconds, therefore 120/360*27= 9

row 4: 16:18 – 16:19 = 60 seconds, therefore 60/360*27 = 4.5

   

ProblemFrequent CauseSolutions
There are surprisingly high requirement peaks! – How can I check if they are plausible? 

The following steps can help understand these peaks:

1) Check if the result is due to the scaling method:

  • Do the peaks disappear, if a shorter interval is selected?
  • Do they disappear, if 100% is selected (to exclude rounding)?

2) Check if the peak is due to a few individual values:

3) Check the underlying data.

In case the peaks are really errors, eliminate the faulty data records or time intervals e.g. with TIS:Zeitbereichsfilter (old Wiki) .

There are only points in time. What shall I do?

 Use "Calculation" to add e.g. 30 minutes to create a virtual interval and then analyse with >>Count only start time<<. “Normal scaling” would not make sense in this case.

The calculated staffing level seems incorrect

This may be due to the interval selected.

If in doubt, try using shorter intervals
Example:

15 minute scaling can produce strange results for intervals of 10 minutes or less: in case of 50% overlapping e.g.

  • 00:00 – 00:10 --> staffing level = 1 in 00:00 – 00:15
  • 00:20 – 00:30 --> staffing level = 1 in 00:15 – 00:30
  • BUT: 00:40 – 00:50 --> staffing level = 0 in 00:30 – 00:40, 00:40 – 00:50, 00:50 – 00:60
  • in case of 100% overlapping --> staffing level = 0

Related topics

  • A detailed description of how to allocate time data along a time raster (time interval) using different calculation methods can be found under Basics: Scaling and time raster
  • This operation can be used to determine how many persons were present in certain time intervals.
  • Please also refer to the operation Sum and count
  • Please execute Link to calendar only after scaling, otherwise two raster intervals will be created per type of calendar day.