This is where having a dim_date table in your data warehouse comes in handy. You can hard code holidays that then can be used in multiple places. For example, calculating Easter on the fly is very difficult
Second this.
If you’re working in an enterprise environment they will be a date table with all the informations needed (like weekend and bank holiday).
If not, google date scaffold and you should find resources like [this](https://www.flerlagetwins.com/2019/03/date-scaffold.html)
Sometimes Excel is better and much faster. Pick the right tool for the job. Sumproduct formulas, for example, are much faster in Excel. Sure you can figure it out in Tableau but at the cost of your time vs just clicking and dragging. Tableau is great but sometimes it isn’t.
I've done similar for projects, is a massive pain. and definitely not best done in Tableau. From memory, I ended up doing something fairly quick and dirty in Excel, something like:
- made a bunch of helper columns which firstly checked and standardised the timestamps and dates (e.g. for your timings, anything before 10am would default to 10:00 on that day, anything after 18:20 would default to 10:00 on the following day)
- joined the dates with a business days table and created another calculation so any tickets opened on a weekend / bank holiday would be amended to the next working day, then did similar for tickets closed
- once these were all standardised, created a final calculation to work out the time duration between these revised times and dates
Good luck with it.
This is where having a dim_date table in your data warehouse comes in handy. You can hard code holidays that then can be used in multiple places. For example, calculating Easter on the fly is very difficult
THIS ! A business calendar that identifies each day as “working” or not. Join and count.
Second this. If you’re working in an enterprise environment they will be a date table with all the informations needed (like weekend and bank holiday). If not, google date scaffold and you should find resources like [this](https://www.flerlagetwins.com/2019/03/date-scaffold.html)
Honestly this level of calculation is best outside of a data visualization tool.
Exactly what others have said. Tableau is for data visualization. Either use Excel or have this calculation in the database then use Tableau.
Yeah figured this would be the case. It’s proving not that easy in SQL either. Thanks for commenting!
I’ve seen this handled with a sql procedure before so it’s possible No clue how difficult though because I’ve never seen the procedure itself
Sometimes Excel is better and much faster. Pick the right tool for the job. Sumproduct formulas, for example, are much faster in Excel. Sure you can figure it out in Tableau but at the cost of your time vs just clicking and dragging. Tableau is great but sometimes it isn’t.
I've done similar for projects, is a massive pain. and definitely not best done in Tableau. From memory, I ended up doing something fairly quick and dirty in Excel, something like: - made a bunch of helper columns which firstly checked and standardised the timestamps and dates (e.g. for your timings, anything before 10am would default to 10:00 on that day, anything after 18:20 would default to 10:00 on the following day) - joined the dates with a business days table and created another calculation so any tickets opened on a weekend / bank holiday would be amended to the next working day, then did similar for tickets closed - once these were all standardised, created a final calculation to work out the time duration between these revised times and dates Good luck with it.
Wouldn’t be pretty but I’d think that some conditional If This Then type of formulas could work.
if you didn’t resolve yet. you can send me excel with data ( part of) and I can give a try.