13 Replies Latest reply on Nov 11, 2016 2:42 PM by Jesse Narang

    Table with different date functions

    Jesse Narang

      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

        • 1. Re: Table with different date functions
          Norbert Maijoor

          Hi Jesse,

           

          Find my approach as reference based on "Scaffolding" below and stored in attached workbook version 9.3

           

          • 2. Re: Table with different date functions
            Jesse Narang

            Hi Norbert,

             

            Thanks for the reply!
            I was wondering if there is there any other way to do this instead of creating the manual data source ? This is relatively a short data sample but my actual data is pretty big and goes back till 2005.

             

            Best Regards,

             

            Jesse

            • 3. Re: Table with different date functions
              Norbert Maijoor

              Hi Jesse!

               

              I am not aware but will ask one of the seniors on the block here.

              Simon Runc do you have an alternative approach instead of "scaffolding" for the scenario described by

              Jesse Narang .

              Upfront..Thanks for your time & attention on this challenge.

              • 4. Re: Table with different date functions
                Jesse Narang

                Thanks Norbert!!

                 

                Regards,

                 

                Jesse

                • 5. Re: Table with different date functions
                  Simon Runc

                  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

                   

                   

                  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.

                  • 6. Re: Table with different date functions
                    Norbert Maijoor

                    Hi Simon,

                     

                    Thanks for prompt action. Much apprecated.

                    • 7. Re: Table with different date functions
                      Simon Runc

                      Not a problem...quite an interesting little challenge!

                       

                      In chart form, it's not too bad like this, as you can dual the 2 dates, but when you want to do some calculations, of this nature, it's much nicer to have it fully normalised.

                      I would also add...that if this were my project, I'd also keep the original shape of data too...so have 2 datasources and blend them, as calculations such as length of employment, are always much easier when the To and From date are in the same row...You could get round it with LoDs and a similar IIF([Hire/Term...]='Hire',[Name],NULL) type thing...but why create the work for yourself. You can then build some Vizes/calculations off the fully-normalised view (where that's the better shape) and others from the orginal, and use blending (and/or cross-datasource filters/actions) to get them all to talk to one another across a dashboard.

                      • 8. Re: Table with different date functions
                        Jesse Narang

                        Sorry for the late reply but thank you so much for your response!!

                        • 9. Re: Table with different date functions
                          Jesse Narang

                          Simon Runc

                           

                          Hi SImon,

                           

                          Seems like I hit a roadblock here. The method worked but:

                           

                          A. Some of the numbers aren't matching 100%

                           

                          B. I created a global filter in my primary data source and since this is the secondary data source I can't use the global filter on this.

                           

                          Is there any way possible we can do this with the existing data without having to normalize using as a secondary data source ?

                           

                          Best Regards,

                           

                          Jesse

                          • 10. Re: Table with different date functions
                            Simon Runc

                            hi Jesse,

                             

                            Are you able to post a workbook, with the issues?

                             

                            ...I'm not sure I understand either point (...sure it's me!), as Attrition wouldn't be 100%? and wasn't aware there was a primary and secondary (unless you went with Norbert's scaffold version)

                            • 11. Re: Table with different date functions
                              Jesse Narang

                              Hi Simon,

                               

                              Thanks for the response!

                               

                              I have attached the workbook and the following is the issue I am having:

                               

                              I do need original hire date's and termination date's for some of the visualizations therefore I imported the same data source which makes it 2 data sources for my workbook- one which is pivoted and other one is not. In dashboard 1 I created a filter action where if you click on a country it takes you to the next dashboard and all the viz's on the next dashboard reflect the changes according to the country you selected from the first dashboard. Since the attrition were calculated from the pivoted data source, that viz doesn't reflect the changes and it stays the same.

                               

                              I hope I was able to explain my problem.

                               

                              Thanks and Best Regards,

                               

                              Jesse

                              • 12. Re: Table with different date functions
                                Simon Runc

                                hi Jesse,

                                 

                                So I think this just that you need to set up the Action, to it filters the seconrary datasource too...

                                 

                                 

                                So I created a [Market (Copy)] version in the attrition datasource (we could have edited the relationships, but if we have the same name in each Tableau just picks it up).

                                 

                                I then set up the bottom section of the Action (Selected Fields) with 2 fields to send...

                                 

                                One like this

                                 

                                and one to send the Market (copy) to the attrition datasource

                                 

                                 

                                That seemed to do the trick...but let me know if it's still not working for you.

                                1 of 1 people found this helpful
                                • 13. Re: Table with different date functions
                                  Jesse Narang

                                  Hi Simon,

                                   

                                  You are the MVP!! This worked for me.

                                   

                                  If I have any other questions or issues then I might reach out to you again.

                                   

                                  Thanks again for your help!!.

                                   

                                  Best Regards,

                                   

                                  Jesse