Looker Calculate Number of Days in a Dashboard Date Filter

[enersesian]

I have a dashboard for my team full of graphs/looks that are all controlled by one dashboard date filter. It defaults to 30 days but is changed frequency by the team, usually for 30, 45, 60, 90, 180, and 365 days. This works fine for most of the graphs, but there are two graphs that rank some team products by data aggregated by the date range of those date filters. The most important one is DAU, and Im ordering our products by cumulative DAU (add up all the DAU in the date range of the date filter), but our director wants average DAU as that's more standardized.

How do I count the number of days that the date filter is set to and put that into a column so I can divide the cumulative DAU column by the number of days in date filter column? I do have access to the LookML as a developer user. I do see some similar answers for Look Studio but it doesn't translation well over to Looker LookML solution. Thanks for the help!

[dananananan]

did you ever get a solution? i am looking for the same.

[RobinA82]

It looks like this may not be possible, however, posting as I need the same, in the slim hope that this will either be possible, or will be developed.

[milanb]

Hey there! This is a former use case I've built. I'll explain the logic and LookML

Here's the logic: You can set a filter-only field in the LookML, which is where users will place their date filter; this is the date_selector. From there you can use liquid syntax to calculate the interval or the range selected; those are the selector_start and selector_end. The duration dimension measures the duration between the start and end and can be used wherever you like in your calculations; this is the selector_interval.

Here's the LookML:

filter: date_selector {
type: date
}

dimension: selector_start {
type: date
sql: {% date_start date_selector %} ;;
hidden: yes
}

dimension: selector_end {
type: date
sql: {% date_end date_selector %} ;;
hidden: yes
}

dimension_group: selector_interval {
type: duration
sql_start: ${selector_start} ;;
sql_end: ${selector_end} ;;
intervals: [day]
hidden: yes
}