Timefilter inside staticColumn in Kibana Canvas

[Vartika_Srivastava]

Hi,

how can we use timefilter inside staticColumn value,but I am getting error - "[staticColumn] > [timefilter] > Can not cast 'datatable' to any of 'filter' "

sample template query :-

timefilter column="time" from="now-24h" to="now"
| demodata
| staticColumn "sample" value={timefilter column="time" from="now-24h" to="now"
| demodata | math "sum(price)"}
| markdown {getCell "sample"}
| render

[tims (Tim Schnell)]

The timefilter function can only be used to filter your dataset by a specific time range. It looks like you are trying to display the difference between your latest timestamp and now, is that correct?

[Vartika_Srivastava]

No, I want to calculate count of some other column value for last 24hrs, which i am unable to get through in one "Elasticsearch SQL query" as a source,because of hard limit on row count

[Andrei_Stefan]

@Vartika_Srivastava I am curious on what SQL query did you use and what was the error/limitation you were getting.

[maolopez (Maurizio Lopez)]

Include in your Lucene query or your SQL query something like : @buildTimestamp[now-1d TO now] or @timestamp[now-7d TO now], like for example:

SELECT COUNT(data.result) FROM "devops-jenkins**" WHERE QUERY('(app_code:9X00) AND (data.buildVariables.NODE_NAME:Agent_OS) @buildTimestamp[ now-7d TO now]') GROUP BY data.result ORDER BY data.result ASC LIMIT 2