Using "Now" In A Dashboard Function Date Comparison With R

[intapiuser]

Overview

This post offers food for thought in regards to how R can be used to retrieve the real-time datetime with the RDouble function. It shows how to run date comparisons using the R syntax.

Challenge

Normally if you want to compare a datetime to "now" you must create a field in the Elasticube using the NOW() function. NOW() creates a timestamp updated at the time of the build but does not reflect ongoing time changes. The challenge appears when the requirement is to compare a datetime field to the actual current time in dashboard function. 

Applications

1. Get hours since the last Elasticube build in an indicator.

2. Compare a datetime integer field from a live connection to the current time without having the current time in a table.

Applying R

After Rserve is running and the connection is confirmed in the Elasticube you can start writing functions in the editor.

R has an array of datetime functions suitable for many tasks. In this post, we will focus on the difftime() function. It returns the difference between two times in seconds, minutes, hours, days, months, or years depending on the arguments passed to it.

RETRIEVING THE CURRENT TIMESTAMP OF THE SERVER

as.numeric(format(Sys.time(), '%Y%m%d%H%M')

This function returns the current datetime of the server.

INTERPRETING A DATETIME INTEGER AS A DATETIME VALUE

time1 <- as.POSIXct(as.character(args[[1]]), format='%Y%m%d%H%M'); 

The code above accepts a value, args[[n]] (third argument of the RDOUBLE function), and parses it as a datetime field. The result is stored in time1.

CALCULATING THE DIFFERENCE BETWEEN TWO DATETIME VALUES

diffs <- difftime(time1, time2, units = 'hour');

This outputs a float value representing the time difference using a time resolution specified by the units arguments.

COMPARING A DATETIME FIELD TO NOW

Final script

RDOUBLE(FALSE,"
        time1 <- as.POSIXct(as.character(args[[1]]), format='%Y%m%d%H%M'); 
        time2 <- as.POSIXct(as.character(args[[2]]), format='%Y%m%d%H%M');
        diffs <- difftime(time1, time2, units = 'hour');
        diffs
        ", [today] , [builtTime])

Today is an alias for the RDOUBLE function described below.

INDICATOR

"Today" component

RDOUBLE(FALSE, "as.numeric(format(Sys.time(), '%Y%m%d%H%M'))" , [# of unique Days in builtTime] )

We can return a single value to the indicator.

PIVOT TABLE / BAR / COLUMN / LINE CHART

"Today" component

RDOUBLE(FALSE, "rep(as.numeric(format(Sys.time(), '%Y%m%d%H%M')) , times = length(args[[1]]))" , [# of unique Days in builtTime] )

We will need to return an array of "todays" equal to the size of our dimensions. If we have 5 rows in our pivot table we need to return "today" 5 times. The "rep" function accomplishes this by duplicating "today" by the number of dimensions.