6 Replies Latest reply on Sep 22, 2015 7:46 AM by jasmine.s

# How do I get the count of each category and total at the end

Hi All,

I have a requirement wherein I have to sum the no of hours for each employee  separately as morning and evening  and have a total on that. a pictorial representation is shown as below. I got the count of the hours for each employee but I am not able to separate by morning and evening. I have created a calculated field for morning (like from 9am to 9pm then morning) evening ( from 9pm to 9am ). Now how do I segregate these based on the hours

Thank you

• ###### 1. Re: How do I get the count of each category and total at the end

You would put that new calc to identify morning/evening on columns.  To get totals go to Analysis > Totals > Select Show Row Grand Totals and Show Column Grand Totals

• ###### 2. Re: How do I get the count of each category and total at the end

Hi Jasmine

It depends on your data... if your data is by employee by hour then you're OK....

I have created some fake source data in this shape below and used it in the attached.

Then I create a Time Period (using hour)

IF [Hour] >= 9 and [Hour] < 21 THEN 'Morning' ELSE 'Evening' END

Next is just building the view...

Cheers

Mark

• ###### 3. Re: How do I get the count of each category and total at the end

Hi Daniel,

I tried that already placed the calculated field in the column.I guess the column which I am referring to extract the morning and time might be the error. can you please tell me how to extract only time from a datetime column. co i need match just the time here

• ###### 4. Re: How do I get the count of each category and total at the end

Then I create a Time Period (using hour)

IF [Hour] >= 9 and [Hour] < 21 THEN 'Morning' ELSE 'Evening' END

You may need to amend the bit in bold depending if you want to include hour 21 (i.e. 9pm) or not

• ###### 5. Re: How do I get the count of each category and total at the end

Hi jasmine,.

Please correct me if my understanding is wrong. I have created a dummy data as per your screenshot and generated the dashboard .This is what you are looking for, if not then please let me know.

 EMP Morning Evening Total EMP 1 3 4 7 EMP 2 4 5 9 EMP 3 5 6 11 EMP 4 6 7 13 EMP 5 7 8 15 EMP 6 8 9 17

• ###### 6. Re: How do I get the count of each category and total at the end

Hi ,

I have attached a mock data in the format how my raw data is. Now from the two columns From and To Timing need to segregate the Morning and evening  for eg 9AM to 9PM - Morning  and 9PM to 9AM evening. Once that is done the count of each employeed on the number of hours worked based on the morning and evening . hope I am able to make you all understand