8 Replies Latest reply on Oct 11, 2016 12:33 PM by ann.stolzman

    Monthly comparison of users

    ann.stolzman

      Hi all,

       

      I'm puzzling through something and I don't know if I can do it in Tableau, so I put it out here for all of you.  I have datasets for each month of the year (going back 3 years).  These include usernames, managers, regions, etc.  We currently assign status' to the users based on whether they exist in the file (by comparing each monthly tab - if they exist in the prior tab, then they're still in the organization; if they don't, then they've left the organization, etc.).  We also do some formulas based on what team they belong to, etc.

       

      Then, using this massive Excel file, we create a stacked bar that shows who is in the org, who left the org, who moved into another organization (by comparing to another list of users), etc. 

       

      Is there a way that I can do this month by month compare in Tableau, so that we can do away with this massive Excel file?  Or does anyone have guidance on how I can do this elsewhere?  I've been struggling with this for awhile now in Tableau:  unioning all the files, and then creating the crosstab with all the user names and whether they exist in that month or not (by count of emailid) and whether they have left the org (if count of email = 1 then they're still there, if 0 they're not), but I can't get it to form into a graph because when you remove the email ids the counts all go wonky (and it only counts the number of unique email ids, without the split between who is there and isn't there), and I'm not sure where to go with this now.

       

      Any ideas?

        • 1. Re: Monthly comparison of users
          Andrew Watson

          I'm quite sure you can do this in Tableau. LOD calcs should help you identify if an employee is still active - you can use the LOD calc to identify the last available file date for each employee and if that date is the same as the latest date of all files (also possible via an LOD calc) you have effectively created a dimension stating whether the employee is active or not.

           

          From that I guess you can do your counts and other aggregations you need to do.

          • 2. Re: Monthly comparison of users
            Luciano Vasconcelos

            I don't know if i'm simplifying too much but you don't need to compare to know who is in organization. It's just read last one.

            If you use this last data to be your main base and make a left join with it you can check changes.

            The only part this doesn't cover is who left.

            Sorry if i didn't understand well.

            • 3. Re: Monthly comparison of users
              ann.stolzman

              Andrew,


              Thanks for the wisdom, but how would I do that?   I know that I can get max date by {Max:(datestamp)}, and I can get the max for the particular user by using {fixed [Emailid]:MAX([Datestamp])}.  That works for me.

               

              However, I have every single month laid out (along with the userids at the moment) and when I try and calculate whether someone has left the organization, I get the result that they've left across all of the periods (including the ones before they joined the org).

               

              The formula I have for determining whether they're in the org is this:

              IF [LOD for joining] < [Email LOD]

              THEN "in Org"

              ELSEIF [Email LOD] < [LOD]

              THEN "Left Org"

              END

               

              This is what I see with all of that (and here's the breakdown of each row)

              The number is an evaluation I did to determine if they exist in that month

              The In Org / Left Org is the calculation that's here

              The first date is the max date of all the files

              The second date is the max date for that user

              The third date is the min date for that user

              In or Out.JPG

               

              Any ideas?  (and, remember with all of this I eventually have to turn this into a stacked bar chart - each of these 5 panes you see are individual users, and once I remove them, will I lose the calculation for whether they're in or out of the organization)

              -Ann-

              • 4. Re: Monthly comparison of users
                Andrew Watson

                It looks like you're along way to getting the solution. I don't fully understand what output you're expecting but the 1s and 0s you have, assuming they're based on the LODs could be summed to give counts of who is in the org in a given month.

                 

                I guess there are a number of things you're trying to do here, all which seem to be similar but may require different ways to solve. Perhaps a good place to start would be to understand each different thing you would like to measure and where you're hitting problems?

                • 5. Re: Monthly comparison of users
                  ann.stolzman

                  I guess I'm trying to do a lot of things.  I have 36 months worth of data that I'm working with.

                   

                  The first thing I need is a stacked chart that shows me the count of who has left the organization, just moved elsewhere in the company, or left the company.  What I'm trying to work through here is who has left the organization and in what month they have left. 

                   

                  Now, the logic we're using is this:

                  If they exist in March's file but not in April's, then they left the company.

                  If they exist in the organization in March but not in the organization in April, then they left the organization.

                  If either of those answers are No, then they haven't moved elsewhere in the company.

                   

                  So I'm working with the full set of data and a subset of the data, based on where they are in the file.  We identify that they're in the organization based on their reporting manager (it's only one person, but that name changes at one point, so I would have to subset data for the organization to be for one manager up to one point in time, and after that point it's another manager. 

                   

                  Then I have another chart that I have to produce that who is new to the organization and who left the organization.  New is currently based on whether or not they existed in the prior file, and I described who left above.

                   

                  The more I think about this, the more I think that it cannot be done in Tableau and that we're going to have to stick with our original Excel model (which is nasty and huge), unless we can find a way to do it in SQL or some other platform (I am ok with simple SQL, and don't have other big data platform experience, so that's kind of a non-starter for me).

                   

                  So, with that, do you have any guidance? 

                  • 6. Re: Monthly comparison of users
                    Andrew Watson

                    This formula should identify those that have left:

                     

                    IF {fixed [Emailid]:MAX([Datestamp])} != {Max:(datestamp)} THEN 1 ELSE 0 END

                     

                    To return the month of leaving this formula should do it: IF [Datestamp] = {fixed [Emailid]:MAX([Datestamp])} AND {fixed [Emailid]:MAX([Datestamp])} != {Max:(datestamp)} THEN 1 ELSE 0 END

                     

                    To identify new: IF {fixed [Emailid]:MIN([Datestamp])} = [Datestamp] THEN 1 ELSE 0 END

                     

                    None of the above has been tested but hopefully it'll point you in the right direction. Some tweaking is likely to be required.

                     

                    In saying all of that I would generally do this churning in SQL before bringing into Tableau, it makes the performance of the Tableau quicker as fewer calculations are required.

                    1 of 1 people found this helpful
                    • 7. Re: Monthly comparison of users
                      ann.stolzman

                      Thanks Andrew! Those work wonderfully, except the first one - I basically changed it to:

                      IF [Datestamp] = {Fixed [Emailid1]: MAX([Datestamp])} THEN 1 ELSE 0 END

                       

                      That is working beautifully for just putting a 1 in the month that the user has left.

                       

                      Now I have some more tricky things to identify a subset out of the entire population, but I'll ask that separately and call this good.

                       

                      Thanks much!

                       

                      -A-

                      • 8. Re: Monthly comparison of users
                        ann.stolzman

                        For anyone still paying attention to this thread, I've started the new one about the next step in this:

                         

                        LOD for Trying to compare users over time