I’m trying to make a generic view to allow “Top N” rankings on pivotted Looks (eg. line-chart timeseries). I’ve followed https://blog.montrealanalytics.com/around-the-block-lookml-for-a-top-n-list-and-other-bucket-af44567... which provided me a great basis on how to get a Native Derived Table to join against my explore with dynamic rankings and respecting the source filters.
I went a step beyond what’s described, and made the dimesnions + measures to be aggregated dynamic, and controlled by fields in the source view. Effectively:
view: top_n {
derived_table: {
explore_source: SOURCE_EXPLORE_NAME {
bind_all_filters: yes
column: aggregate_dimension { field: SOURCE_EXPLORE_NAME.top_n_aggregate_dimension }
column: total_value { field: SOURCE_EXPLORE_NAME.top_n_total_value }
derived_column: ranking {
sql: RANK() OVER (ORDER BY total_value DESC ) ;;
}
}
I define `top_n_aggregate_dimension` and `top_n_total_value` in my source view, follwoing the same principle from the article of creating parameters to select the interested dimension /. measure, and then mapping the values of those dimensions / measures to the fields used by my top_n NDT. eg:
parameter: top_n_total_value_selector {
type: unquoted
allowed_value: {
label: "Total Cost NET"
value: "total_cost_net"
}
allowed_value: {
label: "Total Cost GROSS"
value: "total_cost_gross"
}
default_value: "total_cost_net"
}
measure: top_n_total_value {
type: number
sql:
{% if top_n_total_value_selector._parameter_value == "total_cost_net" %}
${total_cost_net}
{% elsif top_n_total_value_selector._parameter_value == "total_cost_gross" %}
${total_cost_gross}
{% endif %}
;;
}
At this point, I have an NDT that allows me to calculate Top N over any of the relevant dimension/measures in my source table, without having to define several top_n NDTs for the multiple possible permutation which is great.
I’ve been trying to go a step further, and have this as a fully generic NDT, which could be joined against any explore to generate Top N in the same way (ie. source explore would implement the needed Top N dimension/measure, and be joined with this NDT in its Explore). However, I can’t work out how I would make the `explore_source` filed in the NDT take on a dynamic value (currently has to be hard-coded to the name of the explore).
Is therew a way to achieve this eg. using liquid? What i’d like to do is say `explore_source: EXPLORE_THIS_IS_JOINED_INTO` , such that my NDT would be built on top of any explore it is joined into.
Thanks in advance,
Jose
Did you ever figure out a solution for this?