Question

How create a calculated column without including original column in the 'GROUP BY'?

  • 20 October 2021
  • 3 replies
  • 24 views

I have a table with orders, from which I wanna use the ‘DATE’ column and the ‘PRICE’ column. The thing is ‘DATE’ is a date-time value and I’m interested in using only the hour of the day from that timestamp, so I can sum all order prices within an hour, an plot that.

So I created a new column extracting the hour, but then I can’t sum the prices because the original ‘DATE’ column is still in the ‘group by’, so I’d like to get rid of it. Is this possible?


3 replies

Userlevel 6
Badge

Maybe you just want to create a calculated measure (which filters to the specific hour).

https://docs.looker.com/exploring-data/adding-fields/custom-measure#adding_a_filter_to_a_custom_measure

@IanT it seems that would be a possible solution but I don’t have access to create calculated measures. Thanks anyways

Userlevel 6
Badge

I was assuming that you wanted to have the total price for hour 1 alongside the total price for day. Just filter to the hour datepart in the date dimension, if not there add it...if you dont have permisisons to edit the lookml and the lowest grain time period you have is day then its impossible to do.

Reply