How do you do 'Latest Data Date'?


#1

hi, I want to do a filter on a report that’s say, Last 8 weeks. But instead of it being based on ‘Today’, say my data is 5 days old, I need it to be the last 8 weeks to the latest day I have data.

In some BI tools, you can create a date variable that’s populated by a query, say, Select MAX(Order Date) FROM Table; then use that to filter a report.

is something like this possible in Chartio?


#2

In order to filter on the last data date, you would need to edit the SQL in SQL mode. You can replace the SYSDATE in the WHERE statement with the date column.

i.e. FROM:  WHERE ((“table”.“dateColumn” AT TIME ZONE ‘UTC’) BETWEEN SYSDATE - INTERVAL ‘8 WEEK’ AND SYSDATE)

TO:  WHERE ((“table”.“dateColumn” AT TIME ZONE ‘UTC’) BETWEEN (“table”.“dateColumn” AT TIME ZONE ‘UTC’) - INTERVAL ‘8 WEEK’ AND (“table”.“dateColumn” AT TIME ZONE ‘UTC’))


#3

hi Ryan,
thanks for this, it seems straightforward. i’ll give it a try.