How to convert hour in number to human readable like 1 AM, 2 AM ..

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.. 

90f3d21c-2649-4659-9557-e0e343318aa9.png

Can you tell me how to achieve this?

Solved Solved
0 12 1,270
1 ACCEPTED SOLUTION

Dawid
Participant V

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: 

88504965-be2c-45f9-8981-6e0bdb2ba78d.png
1cdbc0d7-1101-4171-b8a6-d004cbac01e9.png

View solution in original post

12 REPLIES 12

leobardor
Participant V

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:

1acb9ce7-b61c-40a1-984d-b529081e4e51.png

2.- Save the changes in your LookML and open the explore , you will find the new timeframe available in your dimension.

Example:

b0d4baaa-81fa-4cec-b37a-2a69f9c92b33.png

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)

43cb71da-7d4b-4c52-9a09-474b036ba51e.png

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 

4953786f-3468-44d2-a5c0-50116b2ad9e9.png

Then I went to Explore and selected Period Start Hour and Measure

e18bc6ea-db97-407b-8942-28feffb0962d.png

Then I added filter on hour for last 24 hours but it is failing:

f76006ad-ce3f-448d-852d-3b6475b292bb.png

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?

Dawid
Participant V

To your dimension definition add

datatype:timestamp

leobardor
Participant V

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:

67dd638c-7892-4d4b-849b-9c7811fb67f9.png

Best regards,

Leo

This is what I tried:

0f16b16f-795a-4609-bb96-53d6b77aee46.png

Then I went to Explore:

3d272359-2511-4ac2-973d-bf7602630c38.png

Still no luck

Dawid
Participant V

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. 

14a60341-6ae7-4d2d-b72a-02623742bd8c.png

Can you please help me to create below chart?
 

24f1c598-0f91-4540-b55a-e23359b41173.png

Dawid
Participant V

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

1906c3a1-f7f5-445b-a676-0cc36d00ad62.png

Dawid
Participant V

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: 

88504965-be2c-45f9-8981-6e0bdb2ba78d.png
1cdbc0d7-1101-4171-b8a6-d004cbac01e9.png

Finally I have my goal:

cdf799d3-e3fa-4773-8bcf-7468fa4c8cb0.png
48212444-165c-4781-becf-f773f398cf95.png
Top Labels in this Space
Top Solution Authors