3 Replies Latest reply on Jun 19, 2013 12:00 PM by Andrew Drinkwater

    Max Date by Account ID




      I'm trying to get the "max date" for a list of account owners by account ID.  My data looks like this:


      Account ID     User     Date          EndResult

      1                    A          1/1/2010     0

      1                    A          2/1/2010     0

      1                    A          3/1/2010     1


      From here I need a distinct count of Account IDs, the Max Date associated with that ID, and a sum of the end results (by user).  Final needs to be:


      User A:

      March 2010, 1 Account, 3/1/2010, EndResult=1.


      Any help would be great!

        • 1. Re: Max Date by Account ID
          Andrew Drinkwater

          Hi Joseph, some of this is built in I think.

          Count Distinct is an option in Tableau as long as you have a data extract.

          For the date, if you make a calculated field with this text:


          - As long as you have accountID or userID on the view, it should pick up the correct date.


          Could there be multiple accounts per user? Or multiple users per account? This could make it a bit trickier, but still possible.

          • 2. Re: Max Date by Account ID

            Thanks Andrew,


            There are multiple accounts for each user which is why it's a little tricky.  Each user has a series of "activities" for each account in this dataset which get a date associated with them.  I'm trying to get a distinct count of Accounts by user, and then associate that count to the max date of all the activities.


            Thanks again for the help!

            • 3. Re: Max Date by Account ID
              Andrew Drinkwater

              Okay, still shouldn't be too bad.


              If you have an extract, you should be able to use something resembling this for the count of accounts per user.

              str(countd([AccountID])) + " Accounts"

              the max([Date]) should work fine as long as you have a user on the view.


              Basically, I put User, Count Distinct AccountID, Max Date, and Sum(End Result) onto the view, and it displays more or less what you have in your example above. If you're meticulous, you can do some if statements based on the count distinct to determine whether there is one or more than one, and add or drop the 's' from accounts accordingly.


              Good luck!