Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Use filter attribute to calculate user value

  • 16 August 2021
  • 2 replies
  • 186 views
fernando.carvalho

Hi everyone.

One of the data columns that I need to display in looker is calculated using the end_date of the chosen period. So my intention is to allow the user to filter choosing the last date ( or maybe a date range ) from a period. With the last date of this period the value of my column would change, something like ( active, inactive ), this value is being calculated using table calculations.

I would like to know if there is any way to get the value of the filter to use in my table calculation?

icon

Best answer by fernando.carvalho 18 August 2021, 22:46

View original
This topic has been closed for comments

2 replies

fernando.carvalho

I was able to create the behavior that I desire using parameters. but It's not totally right yet and causing me some problems. This is how I define the parameter

  #this parameter is being used to filter the data
  parameter: effective_date {
    type: date
  }

  #The dimension just exists to bring the parameter value into the explore
  #in a way that a table calc can reference it
  dimension: pass_effective_date {
    type: date
    sql:  {% parameter effective_date %} ;;
  }

and the table calculations for the field is something like: 

if(
	is_null(${table.end_date})
		OR
	${table.pass_effective_date} < ${table.end_date},
		"Active",
     	"Inactive")
)

I'm also using this parameter to create a custom filter.

This solution is only working if the user choose the filter as "Is on the Day" or "In anytime", other filters which gives the user an option for data range, causes the dashboard to bring no result, which I suspect is related to the value of the parameter while doing the table calculation

fernando.carvalho

I was able to solve this problem today, will share the solution that might help someone in the future. Btw, I think that the title of this question don't really describes the problem, but I wasn't able to change it.

I left the idea of using parameters aside, and used liquid variables to solve the issue.

dimension: state {
    type: string
    sql:
    CASE
      WHEN
       ${view_name.start_date} IS NULL OR 
       COALESCE({% date_end filter_field_name %}, TIMESTAMP(CURRENT_DATE())) < ${view_name.start_date} THEN 'Inactive'
      ELSE
      'Inactive'
    END ;;
  }

where  this {% date_end filter_field_name %} is used to get the end date of my filter.

This will change the value from state depending on the last date of my filter. For now, seem like it's only working with date ranges, but is enough for my current problem.

Not sure if there is a better way of doing this, but if you know a better way, please share with me :)