New to metabase and I am struggling with trend scorecards although I have been able to bypass the strict rule of having a time field in the group by when you only have a current value and not a time series column by using something like now() in the group by. I have this set of metrics I want to show but now in more complex queries with joins and/or the latest metabase update, the fake time grouping hack doesn’t work anymore.
example:
insert a bunch of window functions
SELECT
….
toDateTime(now()) as now
FROM deduplicated_sales
CROSS JOIN window_logic wl
WHERE site_id = {{site_id}}
GROUP BY now
There is no such rule, at least for SQL questions.
The trend viz is very simple, it needs 2 rows with 2 columns, a timestamp and a value. The first row becomes the big number and the second row becomes the comparison. The timestamps need to differ by the amount chosen in the viz config so the correct label is shown.
If you need to, put your data query in a CTE and make 2 parent queries, one that generates the current value, one that generates the comparison value, and combine them with UNION ALL.
now is a reserved word in most database engines, I would avoid using it as a column alias.
