6 Replies Latest reply: Oct 19, 2015 4:45 AM by Robert di rosa RSS

    Group time values

      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.

       

      time group.png

        • Re: Group time values
          Andrew Mein

          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

              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
                  Andrew Mein

                  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:

                  TimeGroup.png

                    • Re: Group time values

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

                       

                      time group.png

                       

                      Thank you one more time for this helpfull first part

                        • Re: Group time values
                          Andrew Mein

                          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)