Work Time Spent

Follow

Master Yoda

December 21, 2019 05:34

Hi, 
I am trying to make a dashboard that will display the total number of hours spent in working by a person in a year. What I have in my database is a timestamp which is date and time in one column. 

Any assistance is greatly appreciated. 

Thanks

0

• 

  Hamza Jap-Tjong December 23, 2019 08:05

  I do not know how your table looks like specifically , however you can use date-functions to extra for example a timestamp. If you have a start time and an endtime you could subtract these two from eachother.

  Functions you could use:

  • GETYEAR(datetime)
  • GETQUARTER(datetime)
  • GETMONTH(datetime)
  • GETDAY(datetime)
  • GETHOUR(datetime)
  • GETMINUTE(datetime)
  • GETSECOND(datetime)

  If you can post a screenshot of your tabel, I could answer your question more correctly

  0

  Comment actions Permalink
• 

  Ido Darnell December 23, 2019 16:37

  You could also use the time difference functions,

  YEARDIFF(end,start)

  QUARTERDIFF(end,start)

  MONTHDIFF(end,start)

  DAYDIFF(end,start)

  HOURDIFF(end,start)

  MINUTEDIFF(end,start)

  SECONDDIFF(end,start)

  If you have only the beginning time stamp, and you want to see how much time passed since then, you could utilize the now() function:

  DAYDIFF(now(),[timestamp])

   

  LMK if you'd like any further assistance here,

  ido.darnell@qbeeq.pl

  qbeeq.pl

   

  0

  Comment actions Permalink
• 

  Ido Darnell December 23, 2019 16:39

  Rereading here, if you have the date in one column and the timestamp in another, you will need to merge them into a date-time format, so you will need to use the add-time functions as Hamza stated above to merge them into a single column,

  Thanks Hamza, looking forward to speaking!

  Ido

   

  0

  Comment actions Permalink
• 

  Master Yoda December 24, 2019 12:03

  Thanks for the info Ido and Hamza. 
  
   I have here is only a timestamp column. I only used the count(hours in timestamp) and I was able to count the total hours spent by employee in a month. But I can't get the correct percentage on each activity. (i'm using pivot on this which in rows I have the name of the employees and the activities -- in column part is the months Jan-Dec )

  Result I want to achieve is the percentage of the time spent doing each activities.

   

  For example the total hrs in the month of January of employee A is 26 hrs 

  Time spent in activity  A = 5 hrs = 19.23 %

  Time spent in activity B = 10 hrs = 38.46%

  Time spent  in activity C = 11 hrs = 42.30%
  
  

  This is the result I want to achieve. 

   

  0

  Comment actions Permalink
• 

  Ido Darnell December 25, 2019 10:18

  Hi,

  You could use the 'Contribution' quick function:

  https://documentation.sisense.com/latest/creating-dashboards/using-formulas/quick-functions.htm

  That should achieve that result

  0

  Comment actions Permalink

Please sign in to leave a comment.