Forum Discussion

mrolly's avatar
mrolly
Regular Visitor
1 year ago
Solved

Help: Insert Shift into Data Table Based On Time Parameters

Hello Internet Friends,

 

I'm having trouble figuring out how to insert a column into a datatable that I'm querying in, to show what particular shift a Transaction Time fell within. I'll then be using this Shift information to create Data Slicer filters for 1st and 2nd Shift for analytical purposes.

 

At where I currently work, there are 2 shifts. There are:

1st Shift = 7:00 AM (Current Day) to 3:30 PM (Current Day)

2nd Shift = 3:30 PM (Current Day) to 1:00 AM (Next Day)

 

Ideally, if the Transaction Time falls within the time range between 7 AM to 3:30 PM, then it would show 1st Shift and the same the Transaction Time fell within the time range of 3:30 PM to 1:00 AM (Next Day), then it would show 2nd Shift.

 

Any help would be much appreciated! I also have a Simple Data Table (shown below) that I created, perhaps there's a better solution to add Shift Parameters in there in a column, not sure. Open to whatever! Thank you!

 

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi mrolly ,

     

    try to write a calculated column like:

    Column = 

    VAR _time = [Transaction Time] -TRUNC([Transaction Time])

    RETURN

    SWITCH(

        TRUE(),

        _time >= 7/24&&_time<15.5/24,

        "Shift1",

        _time >=15.5/24&&_time<1/24,

        "Shift2"

    )

3 Replies

  • mrolly's avatar
    mrolly
    Regular Visitor
    1 year ago

    FreemanZ , I played around with it a little bit and figured it out! Just adding the solution that worked for me here for anybody in the future that is trying to do this so they have the working calc!

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi mrolly ,

     

    try to write a calculated column like:

    Column = 

    VAR _time = [Transaction Time] -TRUNC([Transaction Time])

    RETURN

    SWITCH(

        TRUE(),

        _time >= 7/24&&_time<15.5/24,

        "Shift1",

        _time >=15.5/24&&_time<1/24,

        "Shift2"

    )

    • mrolly's avatar
      mrolly
      Regular Visitor
      1 year ago

      Hey FreemanZ ! Thank you so much for taking an attempt at this. I inserted your formula into the Table and it returned the 1st Shift properly, but failed on the 2nd Shift. I think it may have to do with the AM/PM of the Transaction Times, but I'm not certain. Have a look down below.

       

      Maybe it's adding a statement that if Blank, show the text "2nd Shift"?