Group data by time range.

[former_member214071]

Good day everyone ,

I have a data set with records by hour, I would like to know how can group by time range .   The image in the left is how the data is and in the right how we would like to display it.

Thanks in advance,

[Former Member · Accepted solution]

Hello Josue,

Please create Dimension H24 as

[H24]=ToDate([TimeObject];"h:mm a")

and then Create another Dimension Check as

[Check]=If [H24] Between(ToDate("6:00 PM";"h:mm a");ToDate("6:59 PM";"h:mm a")) Then "6:00 PM to 7:00 PM"

Else If [H24] Between(ToDate("7:00 PM";"h:mm a");ToDate("7:59 PM";"h:mm a")) Then "7:00 PM to 8:00 PM"

Else If [H24] Between(ToDate("8:00 PM";"h:mm a");ToDate("8:59 PM";"h:mm a")) Then "8:00 PM to 9:00 PM"

Else If [H24] Between(ToDate("9:00 PM";"h:mm a");ToDate("10:59 PM";"h:mm a")) Then"9:00 PM to 10:00 PM"

Then just drag Dimension [Check] and count Object that is showing in scond column of your screen shot.

Make sure you have unchecked Avoid Duplicate Row Aggregation in your Block Properties.

Regards

Niraj

[former_member214071 · Accepted solution]

Hello Niraj,

The first variable [H24]=ToDate([TimeObject];"h:mm a") turns error , I tried multiple data types include currentime() function,  however your logic is nice and clean.

Will try to figure out why ToDate is no working and let you know.

Thanks.

[Former Member · Accepted solution]

Hello Josue,

Try with [H24]=FormatDate([TimeObject];"h:mm a").

Thanks

[former_member214071 · Accepted solution]

formatdate did the trick, then the check was simple text comparison

If [h24] Between("10:00 AM";"10:59 AM") Then "10:00 AM to 11:00 AM"

Thnkas

[Former Member]

Hi Josue,

Just to know, are you having AM and PM hours?. If you have only PM hours you can try this:

Try the following:

1) convert the time object in string

2) create a variable: if lenght(time object in string)=8 then substr(time object in string);1;2) else substr(time object in string;1;1).

Note: You are going to have a lenght of 7 or 8 depending if the hour is greater than 9 or not.

3) Drag this variable to check that you have only the hour.

4) convert this variable to number, create a new one: ToNumber(VARIABLE)

5) finally, create a new variable: if ToNumber(VARIABLE) =1 then "1:00 PM to 2:00 PM" elseif ToNumber(VARIABLE) =2 then "2:00 PM to 3:00 PM" and so on with the other ones. then create a table, drag this variable and the qty object.

Let me know if you have AM and PM hours, then we can think something with this.