Define dimension from a sub table's column

[Xuanfengxiaozi]

I have a derived table on view, the sql goes like:

with events AS (

    select create_time, export_duration, upload_duration from datasets

)

select ‘export’ AS phase, min(export_duration), max(export_duration) from events

UNION

select ‘upload’ AS phase, min(upload_duration), max(upload_duration) from events

Since min and max are aggregate function, I cannot put create_time within the outside select clause, but I need to define a dimension based on create_time (so I can add a filter on top of that).

[Dawid · Accepted solution]

For this you will need to use a parameter + Liquid statements in your derived tables.

First point would be to read more about the parameters here: https://docs.looker.com/reference/field-params/parameter

The section that will be of the most interest to you is this one:

Once you have that, you need to look at how to insert the values of user’s input to the SQL code. Templated filters is what you need: https://docs.looker.com/data-modeling/learning-lookml/templated-filters#templated_filters

[Xuanfengxiaozi]

It works, thank you so much!