Em, thanks for posting both a sample workbook and the Excel file. So the issue you're having is you are using Leave dates on the column shelf, but then running count the Entry dates. When you uncheck Analysis\Aggregate Measures you get this view:
By using the Leave date the Entry dates that don't match a Leave date produce a Null value. These get counted first then T moves onto the months that have a match. You can see that the 6/5/2012 shouldn't be in the first quarter, and this is what is throwing the count off by one.
In the attached workbook I switched from using Leave to using Entry and this gives a correct count for Entry. But you have other problems. You are using a date part instead of a date value:
So you're essentially telling Tableau to ignore the Year and only aggregate on the month. If this is really what you want to look at then just turn on Show Missing values and you'll you're good to go. You counts will look like this:
If that's not what you're looking for, then one way to attack this sort of problem is to reshape your data either at the data source or in a custom SQL connection. In the attached file I reshaped the data so it looks like this:
Let me know if you have questions.
attrition rate - SW.twbx 38.4 KB
This is awesome Shawn! thank you very much for your help and the comprehensive explanation!
I will have to reshape the data, then, as you suggested.
If possible, would you please explain me (or point me to the doc) how I can add a subTable to my data by using a custom SQL?
As a preference I would let Tableau do the reshape for me rather than doing it at the source.
P.S.For the sake of the completeness, I just want to rewrite here the formula I'm using for the attrition rate (where the denominator is the average of actual employees in the company during the given month)
Attr. Rate = [Sum of LEAVE] / Denominator
Denominator = ((
LOOKUP(RUNNING_SUM([Sum of ENTRYs]),-1) - LOOKUP(RUNNING_SUM([Sum of LEAVEs]), -1) ) +
(RUNNING_SUM([Sum of ENTRYs]) - RUNNING_SUM([Sum of LEAVEs])) ) / 2
Lets see, it would be something like this:
SELECT [Personal ID Number]
, [Entry Date] AS Date
, "Entry" AS Type
SELECT [Personal ID Number]
, [Leaving Date] AS Date
, "Leaving" AS Type
This will basically reshape your to look like the Excel file I put together for you. Is this what you were looking for?
What I am looking for is to know how to write SQL in Tableau? Where to click...
I'm using the latest in trial.
Yes data are in Excel
This was a super thorough explanation!
Unfortunately, I'm stuck as well, even having read through so many of the various community/other site forums.
I'm trying to figure out how to restructure the data as you have with the Term/Hire dates all in one column with their Type in the next column. When I do it, it creates a huge number of NULLS and seems to throw off the data completely.
Can you advise?