9 Replies Latest reply on Apr 12, 2017 6:31 AM by Karen Egypt

    Help!  Table Calculation/Filter Problem?

    Karen Egypt

      I am trying to create a dashboard that compares actual enrollment to the target (benchmark) enrollment that can be filtered by school, department, program, or college (AACC) overall.  The dashboard uses two data files, one data file contains the target enrollment data and the other data file has test "actual" enrollment data.  Both datafiles have Benchmark Type and Benchmark Sub Type which I want to use to filter the data.  Ideally, I want Benchmark Type and Benchmark Sub Type from the actual enrollment data to also filter the target data file.  However, I haven't figure out how to get this to work.  For example, when I select Benchmark Type=Institution and Benchmark Sub Type=AACC, I get the following results (Target_Enrollment is overly inflated because the filter is not being applied):

       

       

      Day of RegistrationMet or Not MetActualTarget_EnrollmentDifference
      1-May-17Not Met1,2355,576-4,341
      22-May-17Not Met2,46911,118-8,649
      5-Jun-17Not Met3,08613,921-10,835
      26-Jun-17Not Met4,07418,356-14,282
      24-Jul-17Not Met6,17327,880-21,707
      7-Aug-17Not Met8,64238,951-30,309
      14-Aug-17Not Met9,50642,827-33,321
      28-Aug-17Not Met12,09854,520-42,422
      29-Sep-17Not Met12,34555,616-43,271

       

       

      The table should have appeared as the following:

       

          

       

      Day of RegistrationMet or Not MetActualCorrect Target_EnrollmentCorrect Difference
      1-May-17Not Met1,2351,390-155
      22-May-17Not Met2,4692,781-312
      5-Jun-17Not Met3,0863,476-390
      26-Jun-17Not Met4,0744,588-514
      24-Jul-17Not Met6,1736,952-779
      7-Aug-17Not Met8,6429,733-1,091
      14-Aug-17Not Met9,50610,706-1,200
      28-Aug-17Not Met12,09813,626-1,528
      29-Sep-17Not Met12,34513,904-1,559

       

       

      Any help in solving this problem would be very much appreciated.

       

      Thanks!

      Karen

        • 1. Re: Help!  Table Calculation/Filter Problem?
          Jim Dehner

          Hi Karen

          I took a look at your 2 data sources and the data is right - not duplicated - but when yo blend the data and link ONLY or the date field your data coming from the secondary (blended source) is multiplied by 4 - But if you also link on your Benchmark Index the data corrects itself

           

           

           

          I can not fully explain what is going on - and I will continue to investigate it

           

          Let me know if this helps

          Jim

          • 2. Re: Help!  Table Calculation/Filter Problem?
            Jim Dehner

            Hi again - I did a little more research

             

             

            see the link  below for a thread on using a cross data base join

            crossdatabase joins 

             

            And this source discusses when to join v blending -

             

            it may help clarify what is going on

             

            Jim

             

            http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#multiple_connections.html#joinvsblend

            • 3. Re: Help!  Table Calculation/Filter Problem?
              Karen Egypt

              Hi Jim,

               

              Thanks for your help.  The data files  should be linked by Registration (Date), Benchmark Type, Benchmark Sub Type, and Term.  A link by the date field alone would return multiple rows.  I thought I already set up the relationship.  However, perhaps I may have not set it up correctly?  I have been trying to get the filters to apply to both data files.  Ideally, the user selects one value from the Benchmark Type filter and one relevant value from the Benchmark Sub Type Filter.  

               

              Karen

              • 4. Re: Help!  Table Calculation/Filter Problem?
                Jim Dehner

                Yes - the file you sent over was only link on Registration date - the other potential links were broken (not red) -

                 

                Relatively easy fix

                If that helped pleas mark the original response as helpful/correct to close the thread

                Jim

                • 5. Re: Help!  Table Calculation/Filter Problem?
                  Karen Egypt

                  Could you explain exactly how you were able to fix this?  Is there a file you can send?

                   

                  Karen

                  • 6. Re: Help!  Table Calculation/Filter Problem?
                    Jim Dehner

                    Sure - unfortunately I'm on a different version of Tableau and you would not be able to open a workbook but I will walk you through it

                    The file that you went over had the 2 worksheets and the 2 data sources - the sheet below is the comparison to actuals that you referenced in your post

                     

                    When I looked at the secondary data source (red dot) the left hand column had only the Registration Date field link connected (red link) all the others are broken - grayed out - and as you see the totals targeted values are incorrect - I did open the data from the primary and secondary sources and saw there were no duplicates in the file that would result in the problem (note there is a rounded off fact of 4 between the correct and incorrect values)

                     

                    I then looked at the filters that you are using and just tried reconnecting the link on Benchmark Type (see below) just click on the gray link and it connects and turns red - Tableau then recalculates the viz and returns the value below - note I did also test Benchmark sub-type and it will also return the correct values

                     

                     

                    You will also want to connect the link on Headcount Table - each worksheet are independent  - and the connections need to be made on each

                     

                    Hope that helps - If you let me know what version you are on I will try to send you something in that format

                     

                    Are you an educator - my wife taught for years and I always try to help out teachers

                     

                    Jim

                    1 of 1 people found this helpful
                    • 7. Re: Help!  Table Calculation/Filter Problem?
                      Karen Egypt

                      Jim,

                       

                      Yes, I am an educator at a community college.  I have both Tableau 10.2 and Tableau 10.0  (I use Tableau 10.0 because some of my colleagues do not have Tableau 10.2 yet).

                       

                      Thanks for your help!

                       

                      Karen

                      • 8. Re: Help!  Table Calculation/Filter Problem?
                        Jim Dehner

                        Attached is a 10.0 version of your workbook

                         

                        This version difference thing with tableau is an issue - I work with clients on different versions and I either have to go into their system to program something or use one of the 3 versions I have on my machines - but it always seems like I have the wrong version

                         

                        Jim

                         

                        If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                        1 of 1 people found this helpful
                        • 9. Re: Help!  Table Calculation/Filter Problem?
                          Karen Egypt

                          Jim,

                           

                          Thanks so much!  I wished Tableau creates a file format that is compatible with multiple Tableau versions.  It's definitely a big headache having to be careful about which version you are using when working with other users.

                           

                          Karen