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
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.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
