Using parameters in aggregate tables

[anique_trip-163]

Hi community, 

We are trying to setup aggregate tables for dashboards that have parameter filters applied. Apparently you cannot use parameters as dimensions in your aggregate table and Looker suggests to move it those fields to the filter section. The parameter we want to apply it to is a date field that we want to setup flexible in the aggregate table, so that when users select different dates in the dashboard the aggregate table will still work (otherwise it only works when that 1 specific date is filtered on). However we don't get this working as it says the parameter only allows a literal date value (e.g. 10-09-2023), so not a period of say "in the last 3 months."  Would anyone know a solution or workaround for this? Thanks!

[katietee12]

I remember encountering the same problem before but couldn't find a solution in Looker.

[PLaSh]

You can set the date as a filter of type date and then create a dimension using Liquid that references the date filter. Once you have that you can operate on that dimension as normal.

Example:

filter: date_range {
  type: date
  description: "Cohort Time"
  default_value: "1 day"
}

dimension: cohort {
  type: string
  sql:
  CASE
    WHEN {% condition date_range %} ${date_day} {% endcondition %} THEN 'In Cohort'
    ELSE 'Not in cohort'
    END ;;
}

[anique_trip-163]

Hey @PLaSh thanks for your reply! I'm not sure if I fully understand how to apply your solution. Could you perhaps help me how I could get that working with this example that I have.

Say I have a date parameter filter, gained_reference_date_filter, which is applied on dashboard level and can be changed by any user to different dates (e.g. 2023/10/08). Currently when I try to get an aggregate table LookM, Looker adds this as a filter like this:

     filters: [
        # "payments.gained_reference_date_filter" was filtered by dashboard. The aggregate table will only optimize against exact match queries.
        payments.gained_reference_date_filter: "2023/10/08",
      ]

So issue here is that it would only work when the user filters on 2023/10/08, and not when any other date is set by the user.

This is my parameter and the dimension it is used in:

parameter: gained_reference_date_filter {
type: date
convert_tz: no
}

Then this dimension uses that input date from the parameter to apply condition to the data:

dimension: is_gained_reference_date_filter_applied {
type: yesno
sql:
{% if payments.gained_reference_date_filter._is_filtered %}
${payments.paid_date} <= DATE({% parameter payments.gained_reference_date_filter %}) AND (${payments.gained_date} IS NULL OR ${payments.gained_date} > DATE({% parameter payments.gained_reference_date_filter %}))
{% else %}
1=1
{% endif %}
;;
}

Would you be able to tell me how I could apply your solution in this example? Thanks a lot! 

[PLaSh]

You probably don't want your table setup that way. The solution I was imagining, was that your aggregate table needs to contain the fields you want to conditionally filter by. So in your above example, you'd want: paid_date, gained_date as dimensions. Then, when you reference that table you'd have normal filters and/or liquid defined cohort dimensions that specify the gained_reference_date.

Additionally, gained_reference_date doesn't make any sense as a range filter. As used you are saying: I would like the data for all days where paid_date is earlier than gained_reference_date_filter. Or, WHERE paid_date < "in the last 3 months". Instead you'd want something like: WHERE paid_date <= current_date() - months(3)