Hi team,
I have a date dimension group which has date in raw form. I need to convert below chart which has hour in 12 hours format.
I have used hour_of_day but this gives me 0,1,2,3,4,5,6… 23.
I need dimension as 12:00 AM, 1:00 AM..
Can you tell me how to achieve this?
Best answer by Dawid
View original
Hi Rohit,
Based on your example, you might want to follow this steps to achieve the output in Looker:
1.- Add the Looker timeframe “hour” in your dimension group.
Example:
2.- Save the changes in your LookML and open the explore , you will find the new timeframe available in your dimension.
Example:
3.- Choose a measure that you want to include in your linear table.
In this step, include the Looker- chart time label format as it is showed in this example:
(Please find the graph in the “vizualization” tab, in my example , I am displaying last 24 hours as a filter)
Hope it helps!
Best regards,
Leo
Hi leobardor,
Thanks for your response. Your response looks promising but it is not working at my side:
I have data for last 2 years.
I have added “hour” into date dimension group
Then I went to Explore and selected Period Start Hour and Measure
Then I added filter on hour for last 24 hours but it is failing:
I think it is failing because 24 is an integer and Hour is a date field
Can you help me to know what I am missing?
+1
To your dimension definition add
datatype:timestamp
Hi Rohit.
Please ensure that your dimension group is time type, you can filter hours directly with “dimension time types”
In my test, I use the same dimension group to filter:
Best regards,
Leo
This is what I tried:
Then I went to Explore:
Still no luck
+1
What if you change it to datatype: date ?
Yes datatype:date makes it working. I am trying to complete the rest of the solution. Thanks Dawid.
My project has 2 years data. My requirement says, there should be only 24 values on X axis. When I select the Hour dimension it brings two years dates with hours.
Can you please help me to create below chart?
+1
You want to use HOUR_OF_DAY as your pivot, so you only get numbers 0-23, HOUR on it’s own is a date+hour
Guys,
I have tried HOUR_OF_DAY but it is showing hours in intigers. Back to my orignal request, I need to show it as 12:00 AM, 1:00 AM...11 PM
+1
Yes the hour_of_day, as it suggest, will return an integer, no way to format it through excel-style because the underlaying data is not a timestamp. If you had a timestamp you would get your whole time series.
You have to group by something, in this case, it’s an integer, otherwise you would need to do some tweaks and perhaps create an hour_of_day dimension with always the same date (so that it only creates 24 groupings) but thanks to that you would be able to use the formatting.
I tested this like that:
Finally I have my goal:
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
