Group time values

[#1]

Hello,

I would like to make a pie chart where i have resolution times, but due to too many different values i need to group them. this pie chart display ticket open times this mean i will need groups like following :

1st group : 1 min to 15 min

2nd group: 16 min to 30 min

3rd group: 30 min to 1 h

4th group: 1 h to 2 h

5th group: 3h to 4h

etc

a solution where i group by half hours is less suited , but can also help.

Sometimes some ticket don't have a time at all , then the value is 00:00

do you have any clue how to achieve that ?   thank you in advance.

[#2]

you could look at a nested IF statement:

'Group' & IF([YourTime] > MakeTime(1,0,0),Hour([YourTime]) + 3,

IF([YourTime] > MakeTime(0,0,0) AND [YourTime] <=MakeTime(0,15,0), 1,

IF([YourTime] > MakeTime(0,15,0) AND [YourTime] <=MakeTime(0,30,0), 2,

IF([YourTime] > MakeTime(0,30,0) AND [YourTime] <=MakeTime(1,0,0), 3,

' - No Ticket')

[#3]

Thank You Andrew,

i tried it , but it didn't gave any result, i' have to work on the way i get my data, i will come back on this topic in couple days

[#4]

morning,

I used:

TempTime:

LOAD

  ThisTime,

        'Group' & IF(ThisTime >= MakeTime(1,0,0),Hour(ThisTime) + 3,

IF(ThisTime > MakeTime(0,0,0) AND ThisTime <=MakeTime(0,15,0), 1,

IF(ThisTime > MakeTime(0,15,0) AND ThisTime <=MakeTime(0,30,0), 2,

IF(ThisTime > MakeTime(0,30,0) AND ThisTime <=MakeTime(1,0,0), 3,

'No Ticket')))) AS Shift_Group;

LOAD

  MakeTime(FLOOR(RecNo()/6),CEIL(RAND()*50),0) AS ThisTime

AutoGenerate 24;

and it returned results:

[#5]

Hello Andrew,

I tried again, if i display it on a table i have the correct anser , but if i select the differents groups it break the link with the rest of the displayded infromations, also i have to show it on a pie chart , so i need a count, per example 120 call closed under 15 min, 83 under 30, etc.

Do you have an idea how to add a count to the conditions  and make so that if i select a group it filter also the information on the display ??

Thank you one more time for this helpfull first part

[#6 · Accepted solution]

morning,

Example Data Load:

Time_with_Group:

LOAD

  *,

        'Group' & IF(ThisTime >= MakeTime(1,0,0),Hour(ThisTime) + 3,

IF(ThisTime > MakeTime(0,0,0) AND ThisTime <=MakeTime(0,15,0), 1,

IF(ThisTime > MakeTime(0,15,0) AND ThisTime <=MakeTime(0,30,0), 2,

IF(ThisTime > MakeTime(0,30,0) AND ThisTime <=MakeTime(1,0,0), 3,

'No Ticket')))) AS Shift_Group;

SQL SELECT

TicketTime     AS ThisTime,

TicketID,

OtherTicketFields

FROM Ticket_Table;

Then you can add Shift_Group as a Pie chart Dimension, with a count of TicketID (for example)

A filter pane with Shift_Group as a Dimension, will let you filter again (although you can do it by clicking segments of the Pie Chart as well)

[#7]

Thank you Andrew , this was the last piece i needed