Default filter widget value set to a simple sql?

Get Help
1

The default filter widget value for the Category filter in the question below is set to 2018. This is not a relative date where the Date filter widget type would suffice as the underlying data is from an annual report for the preceding year that comes out at some point in January or February. From this starting point the Category filter dropdown can be used to specify one or more years from historicals or forecasts but the default must always be the most currently available annual report year.

image
image987×335 120 KB

How can the default year for this question be set to the year of the current 2018 annual report and update itself for the 2019 annual report when it becomes available sometime in early 2020?

How can the daunting task of updating dozens or even hundreds of such questions be accomplished without having to edit each one?

Can someone suggest a workaround in the meantime until the default widget value can be set to a simple sql statement as shown in the illustration?

Does this exist as an issue yet? Searching extensively on GitHub came up with related issues but it would seem someone would have posed the exact same basic question by now:
GitHub Issue #3156
GitHub Issue #7593

||||||||||||||||||||||||||||||||||||
Upvote GitHub Issue #7593 is probably the best option right now for those who find this affects them. Being that the GitHub Issues list has grown to over 2000 open entries at this point, this has been added to the discussion of #7593. In the meantime, for cases that cannot use relative date mechanisms related to now() the best option is a mass change to the default value as suggested by @flamber below.

2

Hi @mesquest
You can probably write a query that updates all the metadata (table report_card, column dataset_query, look for template-tags, then change default).

But since you’re using Field Filter, then until a few days ago, you couldn’t use Complex Default Value, but the upcoming 0.33.4 (or whatever the next release is), you can now do something like this - yes, it will require that you redo all the queries and remove the Default Widget Value.
This is an Postgres example, change the comment syntax (--) and function to match your database.

...
WHERE 1=1 AND [[{{FieldFilter}} --]] date_trunc('year', now() - '1 year'::interval)

Otherwise you would have to wait until one of the two issues, you have linked to, gets resolved.

1 Like