5 Replies Latest reply on Mar 21, 2014 11:08 AM by Todd Stone

    Aggregations

    Todd Stone

      So, I've been working on this all day and I can't get it to do what I need it to do.  Because I have privacy sensitive data, I can't share a twbx, but I think someone can probably help with the idea.

       

      Basically my data consists of audit numbers, and who performed the audit like this:

       

      Audit #   Auditor

      12345     Internal

      12345     CDC

      23456     DOJC

      34567     Internal

      34567     DOJC

      34567     CDC

       

      What I need to do is display a data table to show for each auditor a count of audit.  Like in my sample Audit # 34567 has 3 auditors so it would show up in Internal, DOJC Same and CDC Same, and Audit 23456 would count as DOJC New.  I hope this makes sense.

       

      I have a screen shot below of my attempt to do the aggregation with a calculated field, but it's not exactly working, as you can see in the SQL aggregation, which has been valdated as accurate. (The DBA won't give my Tableau Server execute rights to run the store procedure, so I have to do the aggregation in Tableau).

       

      I thank you in advance for any and all help!

       

       

      3-18-2014 3-48-55 PM.jpg,

        • 1. Re: Aggregations
          Matt Lutton

          Sorry to detract, but I'm just curious  - is there a reason why your DBA doesn't want you to connect directly to the stored procedure?  We gained that ability in 8.1, as you probably know, and it makes for some excellent single source data connections.

          • 2. Re: Aggregations
            Todd Stone

            I wish I knew the answer to that.  Our Tableau server has been running since November, and they just gave our SQL servers read only access like 2 weeks ago.  Before that, we had to put our Excel/Access data sources on some shared space directly on the Tableau server, as they wouldn't even give access to shared drives.  After much pitching and selling, I was finally able to get read only access.

            • 3. Re: Aggregations
              Matt Lutton

              Wow.  It seems they're probably concerned with Security and access to the DB; however, if you can create Extracts from the DB data sources, you won't be connecting live to the DB--does your DBA know this?  Additionally, you can layers of security within Tableau Desktop/Server. 

               

              I just cannot imagine, but I am sure there are others struggling with the same thing.  Sounds like they just need convincing that nobody would be touching the live database, aside from the initial connection to create an extract.  Now, if you NEED live data connections, then I understand the concerns they might have.

              • 4. Re: Aggregations
                Todd Stone

                Yes, they know this, I'm currently using extracts, and have to manually refresh the dashboard because of the access right (I've even tried to used the log in as option, or whatever and it still doesn't work).  The plan is to change it to a live connection as the data is continually updating.

                • 5. Re: Aggregations
                  Todd Stone

                  It took sleeping on and I figured it out.  I made realized I had another data element that was counting the number of times an audit # had an audit:

                   

                  Audit #   Auditor    Count

                  12345     Internal   2

                  12345     CDC       2

                  23456     DOJC    1

                  34567     Internal  3

                  34567     DOJC     3

                  34567     CDC       3

                   

                  I used this Count column as a string to create a Calculated field to determine a new audit:

                   

                  If [Count] = "1" And [Auditor] = 'DOJC' then "DOJC New"  Else [Auditor]

                   

                  And it works just like I needed it to.