Configuring a dynamic explore_source in Native Derived Table?

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

1 1 572
1 REPLY 1

Did you ever figure out a solution for this?

Top Labels in this Space
Top Solution Authors