Automatic filter for 'this month' on dashboard

simon_onfido
Participant IV

I have a user-defined dashboard which normally shows data up to the last complete month. I’ve set its default to a fixed date (1970-01-01) and have logic in my views/explores so they filter for the last complete month if the filter has that value, otherwise using the date of the filter so that users can see the data as it was in previous months.

I’ve been asked to make this more intuitive, i.e. default to the start of the current month, but I can’t see a way to make a dashboard filter dynamic like this without manually resetting the default every month.

I suppose I might be able to create another filter/parameter and do something funky to dynamically generate the options as something like ‘last month’, ‘May 2019’, ‘April 2019’ etc then add more logic to set the original filter based on this but it seems like quite a lot more code to add, when the Explores supporting this dashboard are already quite complicated.

Any other ideas?

0 5 7,214
5 REPLIES 5

[Still a hack of a solution] I’ve done this in the past by using a user_attribute as the default for a dashboard filter. Set the default value (in the user_attribute) to be the first day of the month, and then don’t add users/groups to it. You still need to update the user_attribute’s default value once per month, but you can do that programmatically via the API.

simon_onfido
Participant IV

Thanks @Spencer1 - I did see that option and wonder about using user attributes, but if it needs updating manually I might as well just edit the dashboard filter and reset the default there.

I might have a solution and will report back if I get anywhere with it!

zckymc
Participant III

Could you not use a dimension such as:

  dimension: period_comparison {
    case: {
      when: {
        sql: ${date} >= DATE_TRUNC('month', GETDATE())
          AND ${date} < DATE_TRUNC('day', GETDATE());;
        label: "current_month"
      }

      when: {
        sql: ${date} >= DATE_TRUNC('month', GETDATE() - interval '1 year')
          AND ${date} < DATE_TRUNC('day', GETDATE() - interval '1 year');;
        label: "last_year_same_month_same_point"
      }

      when: {
        sql: ${date} >= DATE_TRUNC('month',GETDATE()) - interval '1 month'
          AND ${date} < DATE_TRUNC('day', GETDATE() - interval '1 month');;
        label: "last_month_same_point"
      }

      else: "unknown"
    }
  }

Have the dashboard default set to Period Comparsion = ‘current_month’, could also pivot across the other cases for quick comparisons

The easiest way to accomplish this is to use the matches (advanced) filter and just use “This Month”. It will always dynamically calculate the current month.

More information on the matches (advanced) filters can be found here:

simon_onfido
Participant IV

Thanks @dkline - I didn’t mention it in my original posting but the field in question is actually a parameter which accepts a single date. This is then used in a few different Explores as a reference point for a number period-over-period calculations in each. Unfortunately that means that this ‘filter’ can only be set to a specific single date, or the value of a user attribute.

Thanks also @zckymc (and welcome!) - again I’m not sure your solution would work for me but I might try something similar and will report back if I get anywhere!

One further note - I did think I’d found a solution last week using the new constants feature. It turns out you can use Liquid to define the value of constants, so I thought I could have constants to dynamically generate both labels ‘This Month’, ‘June 2019’ etc as well as the date values I need elsewhere (2019-07-01, 2019-06-01 etc). It was looking good (especially as you can use variables to define constants which made me smile) but then if I use constants for the parameter’s allowed_value: value and label the Liquid isn’t processed!

Top Labels in this Space
Top Solution Authors