I'm building a question that is pulling entries from a few different tables relative to a cutoff date I add in a date filter variable. Essentially I want to get a snapshot of the state at the end of the month for a finance report.
I put the variable into a CTE for convenience, so I can reuse it in arbitrary joins in the query: WITH time AS ( SELECT {{date}} AS time )
The variable is a required Date with a default:
Now, is there a good way to make this default be relative, either on the question or a dashboard? I'd like a dashboard subscription to the effect of "this was the state at the very end of last month".
I could hard code this using NOW() and a bunch of datetime math, but that seems hackish, and this is a feature that seems like it should exist. Does it?
Metabase is v0.47.7, will upgrade if needed.
Database is Percona/MySQL 8.0.21-12.
As I understand it, no. Field filters attach to specific fields of specific tables, and I need a coordinated filter for several fields across a handful of disparate tables.
Simplified example, I have these two tables, and I want to pick out the last row for the month for each AccountID in AccountBalances and multiply their Amount with the Rate from the last row for their respective CurrencyID in Rates. A field filter could get me the relavant last rows for either table, but AFAIK not for both; I need an actual raw datetime value representing midnight, the cutoff point, not a filter blackbox where I can't poke at the internals. CREATE TABLE 'Rates' ( 'ID' int unsigned NOT NULL AUTO_INCREMENT, 'CurrencyID' tinyint unsigned NOT NULL, 'Rate' decimal(17,11) NOT NULL, 'Date' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE 'AccountBalances' ( 'ID' int unsigned NOT NULL AUTO_INCREMENT, 'AccountID' int unsigned NOT NULL, 'CurrencyID' tinyint unsigned NOT NULL, 'Amount' decimal(19,8) DEFAULT NULL, 'DateAdded' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP );
