Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to pivot or group by a measure?

  • 19 April 2018
  • 1 reply
  • 7966 views
H

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

1 reply

anakarina.lance
Rank

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:



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.



Reply


Terms of Service