Calculation expression on a Timestamp column

[shahmeerarshad]

Hi All,
I have a data module with a field 'Time' containing timestamp values '2020-05-12 02:48:04'. The table contains alot of data but I only want to show the data that is not older than 4 hours. Somthing like Time > currentimestamp - 4 hours.

I only have read rights to the database table, so deleting the data is not an option.

I have tried creating a calculation expression on the Time column but it throws an error while validating the expression

Error
The expression is not valid. XQE-MSR-0019 The result of a calculation cannot be a boolean expression.

I am a newbee to cognos analytics and been stuck on this for a whole day now.
Any help would be appreciated.

Thanks in advance

[bus_pass_man]

That's a filter expression not a calculation.  It's a bit irritating; why can't I create an expression which is a boolean? What's so magic about it? It also happens I have a calculation which uses sub-expressions which resolve to booleans and I could not get the expression editor in 11.1 to allow me to test them in my expression so I had to test them in filters first.  Sorry rant over.  Back to your problem.

You have 11.0.x.  You have several options.

One is to create a calculation in your query subject with the expression which calculates the difference between the current timestamp and your data.  Then select the calculation and create a filter.  Use a range.  Or create a calculation similar to one of the following and filter it.

if
({expression } < 4)
then (1)
else
(0)

Or this:

if

(hour (
_add_hours({the column which has the timestamp in your case GRAPHENV.TIME} ,4)

) < _hour ( current_timestamp)
)
then ( 1)
else
(0)


Another option would be to upgrade to 11.1.x where you can create such a filter expression in the query subject or create a stand alone filter with the expression or implement the 11.0.x option in 11.1.

[shahmeerarshad]

Hi bus_pass_man,
Below calculation did the trick.
if (hour ( _add_hours({the column which has the timestamp in your case GRAPHENV.TIME} ,4)
) < _hour ( current_timestamp)
) then ( 1) else (0)

I have one more question. I am using this data module in a Dashboard. Is there a way to control the add_hours function through the dashboard.If there was a widget that would allow users to dynamically change the hours value. Like if the user wanted to see the 6 hours old data instead of 4?

[bus_pass_man]

I've experimented with prompts in modules.

They don't work in them.  You get unresolved parameter errors, which is not surprising as you never get a prompt control. 

They seem to work in dashboards and reports.  The former doesn't support things like the promptmany macro. This is the case even with promptmany defined in FM packages.

Whether the prompts in modules working in reporting and dashboards is an accident or an adumbration¹ of things to come is a mystery.  If the intention is to replace FM with modules then it would need to be implemented eventually.



1.  Sorry, I'm re-reading the Birth of the Modern by Paul Johnson.  He's used it about 6 times so far in 360 pages.