Question

Complete dynamic date filter

  • 13 September 2021
  • 2 replies
  • 24 views

Hi,


I am currently working on a marketing dashboard, where the business user will be able to dynamically aggregate the data daily, weekly and monthly.

I have also added a filter that compares the current timeframe with the previous one such as:
current date vs previous
current week vs previous
current month vs previous

My problem is that when the aggregation is on week or month, the current data might not be complete. 
for example if today is Wednesday then  at the aggregation my report will compare the previous week with this week until wednesday.


I would like to create a yesno filter that filters on full week/month/quarter.

Could someone tell how could I achieve this?

Thanks in advance!


2 replies

Userlevel 1

My idea will be that you can create 2 dynamic measures, one “current” and one “previous”, and several measures to be brought into these 2 dynamic measures depending on the 2 filters selection. These 2 filters will be parameters.

And then you will have the logic like

measure: current_period {        
label_from_parameter: metric_selector
type: number
sql:
CASE
WHEN {% parameter period %} = 'week' AND {% parameter completed %} = ‘complete’
THEN ${measure_filtered_for_current_complete_week}
WHEN {% parameter period %} = 'week' AND {% parameter completed %} = ‘not_complete’
THEN ${measure_filtered_for_current_week}
etc etc
END ;;
}
The examples of dynamic measures are here:

https://help.looker.com/hc/en-us/articles/360023640634-Dynamic-Measures-with-Parameters

https://help.looker.com/hc/en-us/articles/360023573273-Great-Use-Cases-for-Parameter-Fields

 

And filtered measures will be like

 

measure: measure_filtered_for_current_complete_week {

hidden: yes

type: your aggregation

sql:…

filters: [date_dimension: “one week ago”]

}

Doc: https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures-

Filter expressions: https://docs.looker.com/reference/filter-expressions#relative_dates

 

Hi @olga !

Thanks for getting back to me!


This is my original parameter for time and dynamic dimension filter for the timeframes:
 

parameter: timeframe_picker {
label: "Date Granularity"
type: string
allowed_value: { value: "Day" }
allowed_value: { value: "Week" }
allowed_value: { value: "Month" }
allowed_value: { value: "Quarter" }
default_value: "Day"
}

dimension: dynamic_timeframe {
label: "Dynamic Date"
type: string
sql:
CASE
WHEN {% parameter timeframe_picker %} = 'Day' THEN ${TABLE}."DAY"
WHEN {% parameter timeframe_picker %} = 'Week' THEN date_trunc('week',${TABLE}."DAY")
WHEN {% parameter timeframe_picker %} = 'Month' THEN date_trunc('month',${TABLE}."DAY")
WHEN {% parameter timeframe_picker %} = 'Quarter' THEN date_trunc('quarter',${TABLE}."DAY")
END ;;
}

The ebove works perfectly fine.


However, I have created the followings:

A parameter that stores two values, Yes and No

parameter: complete_timeframe_filter {
type: string
allowed_value: { value: "Yes" }
allowed_value: { value: "No" }
}

and a dimension group with a CASE statement:
​​​​​

dimension_group:  complete_timeframe {
label: "Completed Timeframe Only"
sql:
CASE
WHEN {% parameter complete_timeframe_filter %} "Yes"
AND ${TABLE}."DAY" >= (date_trunc({% parameter timeframe_picker %},${TABLE}."DAY"))
THEN DATEADD({% parameter timeframe_picker %},-1,date_trunc({% parameter timeframe_picker %},${TABLE}."DAY"))
ELSE {% parameter timeframe_picker %},${TABLE}."DAY")
END;;
}


And I am trying using the complete_timeframe_filter as a filter, however the data doesn’t filter out the incomplete week. 

Any ideas on where the problem could be?
Thanks in advance!

Reply