How to pivot or group by a measure?

[haileysims]

Hi,

I have a look which, at a basic level, is 1 dimension (user ID) and 1 measure (number of products used). I am looking to group by the number of products used and see the count of users for each. Does anyone know how to do this?

Bonus points for being able to also segment this by another attribute, like geo.

Requirements:

• Must be in Looker UI (no LookML).
• Must allow measure to be dynamic (i.e. I cannot simply have the measure converted to a dimension as the # of products used can change at any time).
• Must be able to be visualized in order to add to a dashboard

[anakarina_lance]

Hi @haileysims,

This is possible, however a derived table would need to be created. We can have this be dynamic by dimensionalizing the measure through the derived table. I understand you did not want to do this in LookML, however for the time being this isn’t possible, I have let our product team know that you would like this functionality. I’ve outlined the steps below and this link has more detailed instructions on how to accomplish dimensionalizing a measure:

Dimensionalize a Measure: Cohort Tiers on a Count Modeling

The purpose of this article is to understand how to group by information in your database which is only available as a measure. Examples include: Cohort on a lifetime value such as all orders a customer has ever made or count of sales a salesperson made each quarter Compare an order’s date to that customer’s first order date (when first_order_date doesn’t exist as it’s own column in the users table) Let’s dive a little deeper into the first example. Below is a sample portion of an orders ta…

If you have any questions on anything in the link or below please don’t hesitate to ask or email into help.looker.com and we can go through this process together.

How to steps:

• Start by setting up our results in the Explore interface. Choose the appropriate fields, for example a Salesperson Name dimension and the Count of Sales measure. The result might look like this:
  

• Choose “Open in SQL Runner” from the SQL interface.

• Once in the SQL Runner, you can choose “Add to Project” from the gear menu. Don’t forget to remove any row limit clause in the derived table SQL

• Now that you have your Derived Table in a view file, you can create a Dimension based on your Derived Table field. For example, you can create a tier dimension to group Total Sales into groups of 0-10, 11-20, and 21.

• Create the measure that aggregates on the dimension from 4.

• Make sure to define a primary key for this derived table so that the measures show up in the UI.

• Join the new view file into your original Explore. You should now be able to (i) add the tier dimension to your explore and (ii) filter on this new dimension.