Complete dynamic date filter

[kakurebono]

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!

[olga1]

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

[kakurebono]

Hi @olga1 !

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!