Filter timestamp on Analytic View

[Former Member]

Hello, I have a table in a Analytic view. I would like to get only the records between now() and a future date (e.g. 20151231) on a timestamp column I tried using now() in the filter but i am getting an error. How can we achieve this by using the filter in a Analytic view?

[anindya_bose · Accepted solution]

What is the format of your time stamp column ?

[Former Member · Accepted solution]

Thank you for the response.

The timestamp is this format.

[anindya_bose · Accepted solution]

You cannot directly use now() in Filter for Analytic View. because now() is only available in expression editor.

However, you can create an Input Parameter , in the default expression you can put now() , then use this input parameter for filtering.

But, this can be better handled in Calculation View. 

Create two Calculated Column in your projection node, namely NOW and FUTURE_TS .  Give the definition as show in pic .  Use these two new Column in Filter Expression .

[anindya_bose · Accepted solution]

Pics for Calculation view ( could not attach in the original reply , some error )

Downside of doing it in Analytic View, is Input Parameter would always pop up. 

Cheers

Anindya

[rindia · Accepted solution]

The only change in Anindya suggestion is you need to convert timestamp to decimal format in input parameter. While applying filter both the from and to value should be in decimal format.

you can try expression as decfloat(NOW()) with data length Decimal (21,7).

[anindya_bose · Accepted solution]

Hi Raj

Initially I thought format would be a problem in filtering. But then just tried without formatting and found now()  can filter DATETIMESTAMP field from M_TIME_DIMENSION  .

In fact now() and FUTURE_TS in my model has slightly different format ( no 1/1000 of seconds) , but filtering still works.

Cheers

Anindya

[rindia · Accepted solution]

Good to know.