
1. Re: Network Days Calculation with a Twist
Zhouyi Zhang Mar 17, 2019 10:13 PM (in response to Jason Rowntree)Hi, Jason
Can you provide a sample workbook?
ZZ

2. Re: Network Days Calculation with a Twist
Ewald Hofman Mar 18, 2019 8:07 AM (in response to Jason Rowntree)I think what you want is to add the FIXED LOD also to the minimum transition date. With a LOD you can calculate the aggregation for that dimension. In your case it will give you the minimum transition date per author. In the calculation you provided, you calculated the minimum transition date per row (and thus the value was always equal to the row value)
But in your example, all transition dates only differ in time as far as I can tell. It would be nice if you provided clearer what the issue is.

3. Re: Network Days Calculation with a Twist
Jason Rowntree Mar 18, 2019 5:46 PM (in response to Ewald Hofman)Hi Ewald Hofman
Thank you for your assistance.
Apologies that the sample workbook i created was a bit vague, it was difficult to create a subset and scrub all sensitive data and in my haste i didn't create enough variation in the transition dates field. The data set in reality is quite large and there will be a number of transition dates per employee (hence my creating a minimum transition date calculation which also has to verify if an employee is , the time component is irrelevant for calculations at this stage, only interested in days.
In Theory I would envisage the calculation would be something as per below:
=Networkdays(Min(IF(Transition Date> Entry Date,Transition Date,Null)),Entry Date,Holidays)
As I am still learning / understanding how to used LOD and the FIXED functions I am unsure how to use the correct syntax or breakdown the calculations in tableau.
My thought process was to create the Min Transition Calculation which seems to be working fine, and then use that in a Date Diff calculation and strip out weekends. The Date Diff is working ( lag calculation) but i have no idea how to strip out the weekends.
I have updated the workbook with greater variation in the transition dates.

Employee Lag Sample Data.twbx 195.9 KB


4. Re: Network Days Calculation with a Twist
Ewald Hofman Mar 19, 2019 8:05 AM (in response to Jason Rowntree)Thanks for the updated workbook.
Updating the expressions to calculate the Lag per author
Your calculation of Min Transition Date was not correct. It was calculating for each record the minimum transition date for in the context of that row. It would only return NULL if the entry was before the transition date, or the transition date else, as you can see in the image below.
The Lag was then calculating for each row the difference between the Entry of the author and the minimum transition date. You want it to be per author, and that is where LODs kick in.
With an LOD you can define which level of detail you want to calculate the value for. Because you want to get the minimum transition date per author, you use the {FIXED [Author] : XXX } expression. For the XXX you define the calculation you want to use, which is in your case the MIN() function.
If you change the expression of Min Transition Date to
{FIXED [Author] : MIN(IF [Transition Date] > [Entry] THEN [Transition Date] END)}
then you will get this result
As you can see, you get the same value for Min Transition Date per author, no matter what the Transition Date on that row is.
The next step is to update the Lag function, which needs to find the Entry date per author again and compare it with the Minimum Transition date
DATEDIFF('day',{FIXED [Author]:MIN([Entry]) }, [Min Transition Date])
Remove the weekends from the calculation
You are not the first to ask, and there are many good suggestions how to achieve this, including this article: DateDiff for Working days only