3 Replies Latest reply on Jan 10, 2019 2:56 PM by Joe Oppelt

    Compare Differences in String Values Between Two Dates

    Sachiko McDonald

      I am using Tableau Desktop 10.5.5.

       

      Every month, I have an Teradata data source that updates with a list of members purchasing our product and the subcontractor, office and specific salesperson they are assigned to.  The member is assigned to a salesperson, the salesperson is assigned to an office and the office is assigned to the subcontractor.  Any of these assignments can change month to month for existing members and new members also join who were not in the previous month's cohort.  A flag already exists in the database that identifies members who are overall new utilizers. 

       

      We report to the subcontractor at the office level.  I want a flag that would appear if I generate a list of all the assigned members for an office manager to call out which members are "new" to the office and might need extra outreach even if they've been using the product for a while.  I also want to be able to create a churn report so our leadership can see whether a given office is experiencing more turnover month over month or from January to December, say, or attracting more new members than other offices in that region.

       

      If I pull two distinct months of data out in Excel for a given office, I can compare the member lists with vlookup to see how membership changed and identify the members who are new to our business or existing members who are "new" to an office due to an assignment change, but my hope is that there is a way to automate that so as a new month of data is published, Tableau can add that flag for me.

       

      I tried to create a packaged workbook that has the same basic complexities as my source data - the actual data source reports for 700K plus members, updated monthly, with history going back to 2012.

       

      Does anyone have any ideas?

        • 1. Re: Compare Differences in String Values Between Two Dates
          Joe Oppelt

          I have your workbook open.  I'm not clear what you need to do.

           

          Are you saying you need to identify which members are new from one month to the next (within a given office)?

           

          One concern I have here is this:  You said, "   ...  Tableau can add that flag for me.  "

           

          Tableau doesn't write data.  It can calculate values based on the data in the table, and you can display values on a sheet, but you can't update your data source with calculated values.  If that's what you are looking to do, tableau is not your tool.

          • 2. Re: Compare Differences in String Values Between Two Dates
            Sachiko McDonald

            Hi Joe,

             

            Thank you for boiling down my rambling - you've got it right - I need to identify which members are new from one month to the next within a given office.

             

            I do understand that Tableau can't change my source data, but I hope there is some way to highlight the members in one month who are not present in the month I'm comparing to, whether that be a calculated field that returns "true" if the member was present and "false" if the member was not present or some other process.

            • 3. Re: Compare Differences in String Values Between Two Dates
              Joe Oppelt

              In the attached I approached this in a way that is rather specific to your data.

               

              I changed the sheet to put a measure on TEXT instead of the [Member Code] dimension.

               

              Next I made a table calc ( in this case, RUNNING_SUM) that touches all the marks.

               

              If you just did running_count(sum([Number of Records])) and put that on text, you would see the first row show 1, 1, 1.  (It hits 1 on the first month because there is actual data there, and then remains at 1 because no more months have data.)  You would see the next row display 1, 2, 3 because all three months have data.

               

              My calc says:

               

               

               

              if running_count(sum([Number of Records])) = 1 then "NEW" END

               

              When I put that on COLORS, I get the first in any series displaying in orange, and the rest in blue.  We don't see anything orange in the second and third months of the first row because no data is there to be colored  (even though the value of running_count(sum([Number of Records])) is still 1 in the second and third months.)

               

              The key here is using a table calc of some sort.  Table calcs let us "see" other stuff in the table.  Running_sum (of a value that is 1, such as number of records in this case) results in an incrementing count.  We could also use RUNNING_COUNT to do the same thing.  I could have used LOOKUP to look forward or backward in the table.  Any of these can tell us what came before the cell we're currently sitting on.