Force looker to filter between the 1st of the year and today dynamically?

user_7475225 0
Viewed 68

In looker I can set exact date filters or I can set rough filters. I want to be able to define a range dynamically. For example the start of the year up to today would be the goal.

If I select this year I get a bunch of zeros for the months that have not happened yet.

enter image description here

If I set it to the past 7 months (during July) I can see the data I want but as soon as it is the next month then we will be missing the 1st month of the year.

enter image description here

How do I display all months of current year without display months that have not happened yet?

looker data-visualization dashboard filter
2 Answers

You can use the filter expression before 0 days ago to only show dates before the current date, or before 0 months ago to also hide the current (not yet completed) month. You can stack this condition on top of another filter to get the range you want; from the docs:

To get AND logic: Type your conditions, one by one, into multiple date or time filters. For example, you could put after 2014-01-01 into a Created Date filter, then put before 2 days ago into a Created Time filter. This would mean “January 1st, 2014 and after, and before 2 days ago”.

In a look, you can do this by clicking Filter next to your time dimension, then selecting "Advanced", which gives you a text box to write a filter expression in.

You can also use the always_filter parameter to make this the default behavior for an explore.

Since it looks like you don't have any data for those future dates, a final option is to turn off "dimension autofill" so that Looker doesn't create rows for the dates in your time range without data. You do that in the look, by clicking on the field. More info here

user_108130820

One quick work around to displaying Year to Date is to combine advanced filter, especially with time dimension_group.

You can filter on different granularity levels in order to add an AND clause as the example below:

enter image description here

The double filters would result as below:

enter image description here

Obviously, you can hide the columns that are not needed in the visualization afterwards.

Hope it helps!

user_182318760
Related