Question

How to use measure results as a dimension for new aggregation?

  • 22 March 2018
  • 2 replies
  • 355 views

Userlevel 5
Badge

Hey,


I’m looking for an easy way to use measure results as a dimension in outer query without creating a new derived table. Is it possible through defining a new dimension somehow or any other way?


Current query:



SELECT nr_of_orders, count(user_id) 
FROM (
SELECT user_id, count(orders) as nr_of_orders
FROM orders
GROUP BY user_id) trips
GROUP BY nr_of_orders


Wanted result:



SELECT [new dimension], count(user_id) 
FROM orders
GROUP BY 1


2 replies

Best way that I know of is to use a native derived table. Then join that native derived table back on the order table.




like:


view: order_counts_by_user {
derived_table: {
explore_source: your_explore {
column: user_id { field: orders.user_id }
column: nr_of_orders { field: orders.count_orders }
}
}
dimension: user_id {hidden: yes}
dimension: nr_of_orders {hidden: no}
}

Indeed, as Sach suggests, generally you have to do this via a derived table. You could explore using a correlated subquery, but that’s not an approach supported by Looker.

Reply