|
Time Period Calculations
Time Period calculations are similar to Time Window calculations but relative to the current time slice.
|
Function |
Description |
|
CONTINUE_NPREV |
Checks if there was a value in a previous time slice, N time slices back, and also a value for the current time slice. When you have the case “previous had value, and current has a value”, this function returns 1. Otherwise it returns 0. One use case can be to “mark” that a series has a value (not NULL) in the current time slice, when it also had a value in a previous time slice. CONTINUE_NPREV is related to LEAVE_NPREV and JOIN_NPREV. |
|
COUNT_NPREV |
Returns the number of non-null time slice values across the defined range. Otherwise, returns 0. |
|
DATEADD |
Adds an integer value to a specified DATEPART of an input date value, returning the modified value. Valid DATEPART values are YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, and NANOSECONDS. |
|
DATEDIFF |
The count of the specified DATEPART boundaries crossed between the specified StartDate and EndDate. Valid DATEPART values are YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, and NANOSECONDS. |
|
DATEDIFF2 |
The total amount of elapsed time between the StartDate and EndDate expressed in a given unit. Valid DATEPART values are YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, and NANOSECONDS. |
|
DATEDIFF_TO_NOW |
The total amount of elapsed time from Date until NOW expressed in given unit. Valid DATEPART values are YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, and NANOSECONDS. |
|
DATEDIFF_TO_TODAY |
The total amount of elapsed time from Date until Today(start of day) expressed in given unit. Valid DATEPART values are YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, and NANOSECONDS. |
|
DELTA_NPREV |
Alias for “Measure”-NPREV(“Measure”,number). Calculates the change from the value N time slices previous, to the current value. For example, the current time slice counts as 1, the preceding as 2 and the next as 3 time slices previous. That way, the function always considers N consecutive time slices/values – not N consecutive spaces between values. So, if the current value is 2, and the value three steps back (counting the current value as 1) was 5, then DELTA_NPREV is -3. DELTA_NPREV is related to DELTA_PCNT_NPREV |
|
DELTA_PCNT_NPREV |
Alias for “Measure”/NPREV(“Measure”,number)-1. Works exactly like DELTA_NPREV, but instead of delivering the nominal value of Value(N=3)-Value(N=1), it calculates the difference divided by Value(N=3), For example, moving from 5 to 2 gives you DELTA=-3 and DELTA_PCNT = -0.60 (-3/5). DELTA_PCNT_NPREV is related to DELTA_PCNT. |
|
FALL_NPREV |
Detects if a series has had a decreasing value when comparing the current time slice to a previous time slice. If the current value was lower, then the function returns 1. Otherwise it returns 0. FALL_NPREV is related to GAIN_NPREV. |
|
GAIN_NPREV |
Detects if a series has had an increasing value when comparing the current time slice to a previous time slice. If the current value was higher, then the function returns 1. Otherwise it returns 0. GAIN_NPREV is related to FALL_NPREV. |
|
JOIN_NPREV |
Checks if there was a null value in a previous time slice, N time slices back, while there is a value for the current time slice. When you have the case “previous was NULL, and current has value”, this function returns 1. Otherwise, it returns 0. One use case can be to “mark” that a series had a NULL in a previous time slice. JOIN_NPREV is related to LEAVE_NPREV and CONTINUE_NPREV. |
|
LEAVE_NPREV |
Check if there was a value in a previous time slice, N time slices back, while there is NULL for the current time slice. When you have the case “previous had value, and current is NULL”, this function returns 1. Otherwise it returns 0. One use case can be to “mark” that a series has a NULL in the current time slice, when it had a value in a previous time slice. LEAVE_NPREV is related to JOIN_NPREV and CONTINUE_NPREV. |
|
MAX_NPREV |
The maximum value of current time and the value n time slices before that. |
|
MIN_NPREV |
The minimum value of current time and the value n time slices before that. |
|
NPREV |
The value of a measure n time slices previous of the current time. |
|
PRODUCT_NPREV |
The product of the values n time slices previous of the current time. |
|
SMA_NPREV |
The Simple Moving Average for the n time slices up to and including the current time slice. Alias for SUM_NPREV(“Measure”,number)/number. |
|
STDEV_NPREV |
Calculates the standard deviation for a number of preceding time slices. |
|
STDEVP_NPREV |
Calculates the population standard deviation for a number of preceding time slices. |
|
SUM_NPREV |
The sum of the values n time slices up to and including the current time slice. |
|
TO_POSIX |
Converts timestamp values to posix. |
|
TO_POSIXMILLIS |
Converts timestamp values to posixmillis. |
|
WITHIN_PERIOD |
If input date is within the period compared to the current timestamp, then the measure is returned, otherwise null is returned. Usage Example: withinperiod(period, date, measure) where period is either of ["WTD","MTD","QTD","YTD"] "WTD" = week to date "MTD" = month to date "QTD" = quarter to date "YTD" = year to date |
|
NOTE |
When using Time Windows calculations, fields referenced by the calculation should be enclosed in double quotes and NOT square brackets.
|
For example, using the Time Series column PRICE, the following calculations can be created,
|
Change in Price compared to previous time slice |
[PRICE] - NPREV(“PRICE”,1) |
|
Change in Price compared to 5 time slices previously |
[PRICE] - NPREV(“PRICE”,5) |
|
% Change in Price compared to Previous Time slice |
( [PRICE] - NPREV(“PRICE”,1)) / NPREV(“PRICE”,1) |
|
5 Period Moving Average |
SUM_NPREV(“PRICE”,5)/5 |
|
20 Period Moving Average |
SUM_NPREV(“PRICE”,20)/20 |