Time Period Calculations

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 a number that counts the number of time slices that have a value (not NULL) from and including the current time slice, and N steps back.

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 to values to posix.

TO_POSIXMILLIS

Converts timestamp to 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

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