topic Re: Group time values in App Development

Group time values

Fri, 09 Oct 2015 07:54:12 GMT

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.

Re: Group time values

Fri, 09 Oct 2015 09:25:09 GMT

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')

Re: Group time values

Thu, 15 Oct 2015 14:55:43 GMT

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

Re: Group time values

Fri, 16 Oct 2015 07:54:37 GMT

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:

Re: Group time values

Fri, 16 Oct 2015 14:24:52 GMT

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

Re: Group time values

Mon, 19 Oct 2015 07:51:02 GMT

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)

Re: Group time values

Mon, 19 Oct 2015 08:45:44 GMT

Thank you Andrew , this was the last piece i needed