Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago
Solved

Combining multiple columns to add time together

Hello, I have multiple columns that have work time per day in them. These columns represent different pay rate that is payed for the work time. There is 20 rate columns but data really show mainly in...
Need Help
  • JoeBarry's avatar
    JoeBarry
    2 years ago

    ok, create a new column. and add this code 

    each Duration.FromText([Rate]) / #duration(0, 0, 1, 0))

     

    This converts the hours into minutes. Delete the Rate column. Highlight the date column, go to the Ribbion > Home > Group by Operation is Sum and choose the new minutes column to do the sum.

     

    This will group all the minutes in to one day. You can then divide by 60 to get the hours worked.

     

    Thanks

    Joe

JoeBarry's avatar
JoeBarry
Solution Sage
2 years ago

Hi Justas4478 

 

If you want to view the data correctly and do measures on the data, then you need to format your data differently. 

 

Convert the date column from datetime to date. Highlight all the Rate columns and right click and unpivot all columns. You will be left with the Date and a new Attribute and Values column. Change Attribute column name to RateType and Values to Rate. Filter out a null or blank values from the Rate column.

 

When you load the data into the report, you can make various measures because the data is correctly formatted.

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    2 years ago

    JoeBarry It looks better, but how do I combine different rates of same day in to one?

     

    • JoeBarry's avatar
      JoeBarry
      Solution Sage
      2 years ago

      Should the result be 9:00 here?

      • Justas4478's avatar
        Justas4478
        Post Prodigy
        2 years ago

        JoeBarry If we dont look at it as different rates then yes result would be 09:00 

        Since 01:00 is overtime and still needs to be included in total time for the day.