Hello folks,
How would I set up a widget to answer the question "How many tickets have some between X date and today?", without having to continually update each day when I check it?
For instance- this widget is measuring the average ticket count per performance after one of our shows ended.
Fairly straightforward. However, because I want the dates to run up to 'today' (where 'today' is whenever I view it), I find myself having to update the '(Perf) Date' filter for the widget itself. If I tell it to look at the 'Latest Date' setting, it will look at all the performances that exist in that production season (12/31/21 being the last date, this skews the average quite a bit). The initial date (1/3/21 in this case) remains static, but 'today' will change, and I don't want it to look at every single performance, including ones that haven't happened yet.
I know I can set it to only look at 'Today', or 'Yesterday', but is there a way to look at 'X date to today', and have that date range update whenever viewed? I might be missing something simple, but I can't seem to figure it out.
Hi Nathanael,
You're not missing something simple. This will require a more advanced, custom filter to filter on BOTH your hard date to latest date, and dates prior to today. The process for coding this kind of advanced filter is to filter on each of those, and copy the code from the Advanced filter type page out to notepad. For example, I set a Calendar type filter from 3-Jan-2021 to Latest Date, went to the Advanced tab, and copied out this:
{ "from": "2021-01-03"}
Then I changed to a Time Frame of Days in the Last 365 Days, went to the Advanced tab, changed it to 366, and copied that out:
{ "last": { "count": 366, "offset": 0 }}
The format to combine these filter types with AND or OR is:
{ "or":[ { "filterType1": "filterValue" }, { "filterType2": "filterValue" } ]}
Pasting that into the Advanced filter, overwriting what's there, we can then update it to an "and" filter, with the individual filters we want to combine, resulting in:
{ "and": [ { "from": "2020-01-03" }, { "last": { "count": 366, "offset": 0 } } ]}
Click the Test button and you should get results in the right-side preview pane. If you get "Invalid query syntax," double check that the curly brace and square bracket pairs are lining up and are all matched/paired.
Best,Chris
Thank you! This seems to be working. I appreciate your help, as always.