Any detail you can provide about the mockup and your desired results may help. For instance, what is the underlying data and what is being calculated (or what should be calculated in Tableau). I've taken a look, but am not clear on what you are hoping to achieve based on the Excel screenshot alone -- any detail you can provide to help guide volunteers on this Forum may help.
Best of luck!
1 of 1 people found this helpful
This has had me stumped for about 3 days - but I found an old post and worked from there
The process here is to create a running date that can be used as an axis for your charts -
The axis starts at you minimum start date and extends past your last end date
To get the total axis I made a couple of changes in your data set - first I added a record with a start date out late this year
I placed it in Nashville (my home town) so it can easily be hidden -
Next I had to deal with the nulls in the end date - I just added a calculation to give them and end date of 12/31 - could be any date in the far future IFNULL([End Date],DATE('2017-12-31'))
I did not extend the calculations past getting the rep count and attrition and for some reason I am 1 rep off throughout ?
Now this is what the chart form looks
and i graph form
Getting there requires some work
First create a start date = datetrunc('month',WINDOW_MIN(min([S-Date])))
And a running date calculation = dateadd('quarter',index(),[Start Date])
Each are table calculations - the start date is set as show on the left and the running date on the right
Then Create a calculation to determine which are active and set an indicator to 1
if [Running Date] >= window_Max((max([S-Date])) )
and [Running Date]<=WINDOW_MAX(max([cleaned date]))
Then 1 else 0 end
This formula was counter intuitive and had me going for a couple of days - it to is a table calc set as
Note the placement of the metrics and the restart is critical to this process
Finally the number of reps is determined if index() = 1 then window_sum([isactive]) end - this one was a sticking point-
finally I realized that the index in this formula in not the same as the index in the running date formula
it to is a table calc set as shown
Now one last formula - attrition [Number of reps (copy)]-LOOKUP([Number of reps (copy)],-1)
and yes it too is a table calculation
In creating the viz place your data set Start Date (I changed the name to S Date while working with the viz) on the column
Right click it and select Show Missing Values (Critical step)
Next load up the detail tile as shown above and in the attached - - you will have to set all the table calculation settings after you load the detail tile
Ok this was the hard part - the remaining calculations should be much more straight forward
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.