12 Replies Latest reply on Feb 13, 2014 3:48 PM by Jonathan Drummey

Count the number of leave days taken by weekday

Hello,

I am trying to solve a problem and thught I might leverage the Tableau community for help.

I have some HR data that indicates a leave start date and a leave end date. I can calculate the number of days leave using datediff easily enough but I want to total the numbr of leave days by day of week. For instance I begin leave on Wednesday and return the following Thursday.  In my report I need columns to indicate working days (Monday through Friday) and a measure to indicate number of days leave for each. So in th example above Friday, Monday and Tuesday would inidcate 1 each and Wednesday and Thursday would indicate 2.

I cannot think of a calculation to  the number of times leave occured for each weekday. Just to add complexity, I will be reporting on these monthly so if leave crosses into next month I only want to report the total of weekdays days for this month and I will report the rest next month.

• 1. Re: Count the number of leave days taken by weekday

Hi David,

Please attach a sample workbook(.twbx) so that we can suggest you according to that.

Warm Regards,

Prashant Sharma - India | LinkedIn

• 2. Re: Re: Count the number of leave days taken by weekday

Prashant,

I have created some dummy data and a worksheet to show what I am getting and there is also a screenshot in the dashboard showing what I need.

• 3. Re: Count the number of leave days taken by weekday

Hi David,

You have to add date field in your dataset because Tableau does not generate a date field with which we can compare your "Lease Start Date" & "Leave End Date". For that you have to add a date field having all the dates by which we can compare your date field & put that into column section & choose Weekday & after that we can calculate the week day of each date & show the Absent or Present of an employee. But may be someone from community may help you with proper method of doing this.

Warm Regards,

Prashant Sharma - India | LinkedIn

1 of 1 people found this helpful
• 4. Re: Re: Re: Count the number of leave days taken by weekday

David & Prashant,

Tableau actually can do this without any manipulation of the source data (except to add an employee X record for the purpose of domain padding).  I've attached a workbook to demonstrate how it can be done (note that it does not, at this point, take the month division into account -- which is why Employee E gives slightly different results).  However, it is very complex and it would be ideal to have a different structure for the source data that would make working with it in Tableau easier.

Feel free to ask questions or even contact me directly.  I'd be happy to walk through how it works!

Regards,

Joshua

Edit: just noticed a couple of calculations were off.  Updated screenshot and workbook.

1 of 1 people found this helpful
• 5. Re: Count the number of leave days taken by weekday

Hi Joshua,

I got your process. Thank you so much for sharing this. This is a complex one & going to add the step by step method of achieving this in my documents. This process will be helpful when we do not want to change anything in our datasource. Happy learning !

Warm Regards,

Prashant Sharma - India | LinkedIn

• 6. Re: Re: Re: Re: Count the number of leave days taken by weekday

- Alternative #1 uses a UNION query to duplicate the data so there is one row per employee/start date & end date, then domain padding (Show Missing Values) to generate enough data to generate the counts. With this technique, there's enough data in place that we can use the WEEKDAY(Date) pill as a dimension for partitioning the view. This requires a bunch of calculated fields to deal with the padding, just a different set of calculated fields than Josh's solution.

- Alternative #2 uses a cross product query with a table of dates , so there is one row in the data per employee & day of leave date, then the view can be built with four mouse clicks in Tableau. Personally, this would be my preferred solution, it generates a lot of extra rows but is much easier to build & maintain in Tableau. See The Cross Join Collection for more details on this type of query.

Jonathan

• 7. Re: Re: Re: Re: Re: Count the number of leave days taken by weekday

I would also definitely favor the cross product solution  for the reasons Jonathan mentioned.

Just for completeness, here's the workbook with the desired view also split out by month, per the original question,

This was an easy task using Alt #2.  But much more difficult using the domain padding / table calculation approach.  I didn't go through the steps required to convert Alt #1, but it would have been a similar exercise to updating my solution (i.e. quite a bit more difficult).  Also, filtering the months using my solution or Alt #1 would break the solution -- domain padding solutions in Tableau tend to be brittle.  It doesn't mean that you shouldn't use them (indeed, there are cases where you may have to, or where it is advantageous).  But all things being equal and if you have the option, solving this in the back-end data is often preferable.

Regards,

Joshua

• 8. Re: Re: Re: Re: Re: Re: Count the number of leave days taken by weekday

Oops, I missed the month part of the requirements, thanks for pointing that out! I totally agree that would make the domain padding more difficult. It is possible to filter for a particular month, in your latest view I created a table calc filter on the month so it respects the domain padding. However, it's a non-trivial calc with an Advanced... Compute Using so it's just another example of how this is complicated!

Also, the original poster did not say anything about wanting totals, the domain padding solutions would need to be further altered (and made more complex) to work with Tableau's current subtotals & grand totals functionality, whereas with the back-end data reshaping in Alt #2 the subtotals and grand totals "just work".

• 9. Re: Re: Re: Re: Re: Re: Count the number of leave days taken by weekday

Hello all,

Thankyou very much for the assistance, as is usual more than one way to do this but ALT 2 looks less complex. In my real data my rows also have many more measures for each employee that I will need to sum as well. ALT #2 would force me to average these measures and therefore only be able to use totals for the average(Or was there a feature released in 8.1 that allows me to choose how to total?). I could also create another data set for just leave calculations but this will impact my use of filters and therefore make this more complex as I will need to use actions and parameters instead of filters. SO if 8.1 doesn't allow me to total averages, even though ALT #1 may be more complex, it may be a better option depending on what other requirements my end user has. I would be interested in comments about preferred approaches.

• 10. Re: Re: Re: Re: Re: Re: Count the number of leave days taken by weekday

Hi David,

If you're using Alt #2 then you can take advantage of the two-pass totals introduced in 8.1. Something else that might work is to do a data blend back to the unshaped data for those per-employee measures, and if necessary the two-pass totals will also support regular aggregates from blended data sources.

Jonathan

1 of 1 people found this helpful
• 11. Re: Re: Re: Re: Re: Re: Count the number of leave days taken by weekday

Thanks Jonathan,

I do like the new two pass totalling feature in 8.1 and this is an ideal application. Thanks again for your assistance

• 12. Re: Count the number of leave days taken by weekday

You're welcome!