Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have created a times field in 'hh:mm' format. Now I want to further create a dimension that will group the time based on the range below.
What I want to achieve
=if(Date >= 00:00 and Date <= 01:00, '0-1hrs',
if(Date > 01:00 and Date<=02:00, '1-2hrs',
if(Date > 02:00 and Date <=04:00, ' 2 - 4hrs',
if(Date >04:00 and Date <= 06:00, '> 4 <= 6hrs',
'>6hrs'
)))).
I tried the code below but it giving me interval of 1 day instead of hours
=if(Date >= 00 and Date <= 01, '0-1hrs',
if(Date > 01 and Date<=02, '1-2hrs',
if(Date > 02 and Date <=04, ' 2 - 4hrs',
if(Date >04 and Date <= 06, '> 4 <= 6hrs',
'>6hrs'
)))).
Kindly help if you can.
Many Thanks
K
Hi,
Don’t know for sure, nut shouldn’t you use the time format instead of the date format?
What does date field contain. can you share sample app or data.
Looks like you're comparing days not hours.
Thanks,
Hi
The solution depends on the source information and the orientation of the results obtained.
eg. I have an event occurrence list,
One of the ways to solve your requirement is as follows:
Events:
LOAD Id_event,
Date_hour,
text(hour(Date_hour)) & '_' & text(hour(Date_hour)+1) & 'hrs' as Hour_range,
Timestamp(Date_hour,'hh:mm') AS hour_min
FROM
Events_a.csv;
Then the results obtained:
Date_Hour | hour_min | Hour_range |
01/10/2016 00:06:00 | 00:06 | 0_1hrs |
01/10/2016 00:08:47 | 00:08 | 0_1hrs |
01/10/2016 01:33:03 | 01:33 | 1_2hrs |
01/10/2016 01:38:54 | 01:38 | 1_2hrs |
01/10/2016 02:11:07 | 02:11 | 2_3hrs |
01/10/2016 02:11:20 | 02:11 | 2_3hrs |
01/10/2016 02:27:37 | 02:27 | 2_3hrs |
01/10/2016 02:40:54 | 02:40 | 2_3hrs |
01/10/2016 02:41:09 | 02:41 | 2_3hrs |
01/10/2016 03:58:47 | 03:58 | 3_4hrs |
01/10/2016 03:58:59 | 03:58 | 3_4hrs |
01/10/2016 03:59:09 | 03:59 | 3_4hrs |
01/10/2016 03:59:20 | 03:59 | 3_4hrs |
01/10/2016 04:15:04 | 04:15 | 4_5hrs |
01/10/2016 04:15:18 | 04:15 | 4_5hrs |
01/10/2016 05:49:40 | 05:49 | 5_6hrs |
01/10/2016 05:49:41 | 05:49 | 5_6hrs |
01/10/2016 06:00:34 | 06:00 | 6_7hrs |
01/10/2016 06:00:48 | 06:00 | 6_7hrs |
01/10/2016 06:01:03 | 06:01 | 6_7hrs |
01/10/2016 06:01:53 | 06:01 | 6_7hrs |
01/10/2016 07:57:32 | 07:57 | 7_8hrs |
01/10/2016 07:57:45 | 07:57 | 7_8hrs |
01/10/2016 07:57:59 | 07:57 | 7_8hrs |
01/10/2016 07:58:13 | 07:58 | 7_8hrs |
01/10/2016 08:03:05 | 08:03 | 8_9hrs |
01/10/2016 08:06:34 | 08:06 | 8_9hrs |
01/10/2016 08:06:49 | 08:06 | 8_9hrs |
01/10/2016 08:07:02 | 08:07 | 8_9hrs |
01/10/2016 08:07:12 | 08:07 | 8_9hrs |
01/10/2016 09:50:15 | 09:50 | 9_10hrs |
01/10/2016 09:50:57 | 09:50 | 9_10hrs |
01/10/2016 10:00:40 | 10:00 | 10_11hrs |
01/10/2016 10:05:53 | 10:05 | 10_11hrs |
01/10/2016 10:07:09 | 10:07 | 10_11hrs |
01/10/2016 11:29:56 | 11:29 | 11_12hrs |
01/10/2016 11:30:59 | 11:30 | 11_12hrs |
01/10/2016 11:32:53 | 11:32 | 11_12hrs |
01/10/2016 11:33:33 | 11:33 | 11_12hrs |
01/10/2016 12:01:25 | 12:01 | 12_13hrs |
01/10/2016 12:01:49 | 12:01 | 12_13hrs |
01/10/2016 13:51:24 | 13:51 | 13_14hrs |
01/10/2016 13:51:34 | 13:51 | 13_14hrs |
01/10/2016 13:51:47 | 13:51 | 13_14hrs |
01/10/2016 14:00:29 | 14:00 | 14_15hrs |
01/10/2016 14:05:37 | 14:05 | 14_15hrs |
01/10/2016 15:40:06 | 15:40 | 15_16hrs |
01/10/2016 15:40:18 | 15:40 | 15_16hrs |
01/10/2016 15:40:41 | 15:40 | 15_16hrs |
greetings
Moisés Vivanco