What do I need to fill in the alarm template to alarm on a datetime param?
The param is defined as a numeric text and uses the measurement option "datetime" to represent it as a Date and Time using the regional settings of the cube client.
What do I need to fill in the alarm template when I would like to alarm this param if it e.g. is lower then a specific date?
Hi Mieke,
A datetime parameter is internally stored as an "OLE Automation date" (double) that is defined as follows:
An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.
Source: https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate
You could calculate the numeric value for a specific date and use that in the alarm template. I know this is not very user-friendly, but I'm not aware of a better way to do this.
An easy way to calculate the value is by using Excel. Simply change the format of the column from datetime to number:
Hi Mieke,
I remember I saw a similar question (unfortunately I could not find it), but the proposal was to create two extra parameters:
- One that define the timestamp to compare the datetime param
- Another param that display either if the datetime is lower that the timestamp defined in the previous step (discreet param), or display a numerical difference (seconds, hours, days) from the timestamp.
Another option, not alarm template related, is to you use the filtering option in GQI. Via GQI you can compare datetimes:
With a custom operator (there is a custom operator in Github for reference), you could create an extra column where you can apply conditional coloring. This will not generate an alarm, but you could visualize it in a low code app/dashboard.
Another extra option is to use query filters (this option doesn't require a custom operator):
Hope it helps.