Forum Discussion

Marco3007's avatar
Marco3007
Frequent Visitor
3 years ago
Solved

Help creating custom column based on time interval

Greetings!   I have tried, without much knowledge other than some experience, to create a visual that displays incident tickets handled by my team.   I want to present how many tickets have been ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Marco3007 

    try to add a calculated column like:

    column = 
SWITCH(
    TRUE(),
    [INC_Time]<5/24, "2h- 5h(out of bh)",
    [INC_Time]<8.5/24, "6h- 8h30",
    [INC_Time]<11/24, "8h30- 11h",
    [INC_Time]<16/24, "13h30- 16h",
    [INC_Time]<19/24, "16h- 19h",
    "21h -22h"
)

    it worked like:

  • Marco3007's avatar
    Marco3007
    3 years ago

    Thank you very much, FreemanZ!  I will try your suggestion. I had been struggling for a while and eventually made it work with AND function, as well as &&. In the end this is how I set it, but I will give it a try with your code, too:

     

    INC_CreationPeriod = IF([INC_TimeOnly]>=time(6,0,0)&&[INC_TimeOnly]<=time(8,29,59), "6h - 8h30",
    IF([INC_TimeOnly]>=time(8,30,0)&&[INC_TimeOnly]<=time(10,59,59), "8h30 - 11h",
    IF([INC_TimeOnly]>=time(11,0,0)&&[INC_TimeOnly]<=time(13,29,59), "11h - 13h30",
    IF([INC_TimeOnly]>=time(13,30,0)&&[INC_TimeOnly]<=time(15,59,59), "13h30 - 16h",
    IF([INC_TimeOnly]>=time(16,0,0)&&[INC_TimeOnly]<=time(18,59,59), "16h - 19h",
    IF([INC_TimeOnly]>=time(19,0,0)&&[INC_TimeOnly]<=time(21,29,59), "19h - 21h30",
    IF([INC_TimeOnly]>=time(21,30,0)&&[INC_TimeOnly]<=time(1,59,59), "21h30 - 2h", "2h - 6h (out of BH)")))))))
     
     
    Thanks a lot once again!
Marco3007's avatar
Marco3007
Frequent Visitor
3 years ago

An example of what I expect (using made up data):

 

 

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Marco3007 

    try to add a calculated column like:

    column = 
SWITCH(
    TRUE(),
    [INC_Time]<5/24, "2h- 5h(out of bh)",
    [INC_Time]<8.5/24, "6h- 8h30",
    [INC_Time]<11/24, "8h30- 11h",
    [INC_Time]<16/24, "13h30- 16h",
    [INC_Time]<19/24, "16h- 19h",
    "21h -22h"
)

    it worked like:

    • Marco3007's avatar
      Marco3007
      Frequent Visitor
      3 years ago

      Thank you very much, FreemanZ!  I will try your suggestion. I had been struggling for a while and eventually made it work with AND function, as well as &&. In the end this is how I set it, but I will give it a try with your code, too:

       

      INC_CreationPeriod = IF([INC_TimeOnly]>=time(6,0,0)&&[INC_TimeOnly]<=time(8,29,59), "6h - 8h30",
      IF([INC_TimeOnly]>=time(8,30,0)&&[INC_TimeOnly]<=time(10,59,59), "8h30 - 11h",
      IF([INC_TimeOnly]>=time(11,0,0)&&[INC_TimeOnly]<=time(13,29,59), "11h - 13h30",
      IF([INC_TimeOnly]>=time(13,30,0)&&[INC_TimeOnly]<=time(15,59,59), "13h30 - 16h",
      IF([INC_TimeOnly]>=time(16,0,0)&&[INC_TimeOnly]<=time(18,59,59), "16h - 19h",
      IF([INC_TimeOnly]>=time(19,0,0)&&[INC_TimeOnly]<=time(21,29,59), "19h - 21h30",
      IF([INC_TimeOnly]>=time(21,30,0)&&[INC_TimeOnly]<=time(1,59,59), "21h30 - 2h", "2h - 6h (out of BH)")))))))
       
       
      Thanks a lot once again!