Skip to main content
Topics#Tableau Desktop & Web AuthoringTableau Community Forums Member (Inactive)'s Question
Tableau Community Forums Member (Inactive) (Salesforce) asked in #Tableau Desktop & Web Authoring
Nov 4, 2016, 1:15 PM

Table with different date functions

Hi Tableau Community,

 

I have a data source with 2 different dates - Hire Date and Termination Date. I am trying to create a table visualization in Tableau that should show how many people were hired and terminated in a given quarter. Also I am trying to calculate the attrition rate which is basically:

 

No. of terminations in a quarter/Number of Hires in a quarter * 100 %

 

Can someone please guide me on this ?

 

I have attached the workbook also.(Version 9.3).

 

Thanks,

 

Jesse

Sample.twbx
16 answers
  1. Simon Runc (Atheon Analytics)
    Nov 4, 2016, 6:16 PM

    Thanks for the ping Norbert...

     

    hi Jesse,

     

    I can see that the datasource for this is Excel, is that the datasource for the "real world" version of this?...or if it's in SQL do you the ability (..or access to the ability!!) to pivot the data?

     

    If so...I have a solution!. I used the Pivot function for an Excel sheet, to pivot the 2 dates

     

    Thanks for the ping Norbert... hi Jesse, I can see that the datasource for this is Excel, is that the datasource for the

     

    Then once we have our date in a single column...it all becomes a bit easier.

     

    I then create 2 fields, which return the [Name] dependant on if the [Name] is a Hire or Termination

     

    [Hire Name]

    IIF([Hire/Termination] = 'Hire Date',[Name],NULL)

     

    [Termination Name]

    IIF([Hire/Termination] = 'Termination Date',[Name],NULL)

     

    We can then do COUNTD on these fields (NULLs don't get counted in COUNTD)

     

    I can then use the following calc for the rate

    [Attrition]

    COUNTD([Termination Name])/COUNTD([Hire Name])

     

    I also had to create a filter, to filter out the NULL dates (you could just do this as a datasource filter, which would make the number of rows slightly smaller...but unless you are into the 10s of Millions you'll hardly notice the difference in speed!)

     

    Tableau also has the Pivot stored, so if you have new data and refresh the extract it will perform the same pivot, so no extra maintenance there.

     

    Hope this helps, and makes sense...but let me know if not.

Write an answer...
0/9000