1 2 Previous Next 18 Replies Latest reply on Jun 11, 2018 10:23 AM by Jim Dehner

    Percents and counts

    Bishar Sethna

      I am hoping you can help me with some more issues I am having with this workbook.

       

      I am trying to replicate the format below

       

       

      Admit
        Types*
      AppsCompleted% CompletedAdmits% AdmittedEnrollment
      Freshman (FR,
        FU, EQ, AF)
      201890701611.8%13986.3%70
      201782111521.9%13085.5%47
      % change10.5%5.9%6.9%48.9%

       

      I created calculated fields to show how many were accepted and completed but the counts do not show up correctly. Can you please help me.  My totals for the total applicants is correct. However my other totals do not come out correctly.

       

      Speaking of percent change. The calculation in the Tableau workbook does not come out to be correct.  For example the freshman %change should be 10.5 but in the workbook it shows 9.62. . I have attached a revised workbook.

       

      Thank you.

       

      Best,

      Bishar

        • 1. Re: Percents and counts
          Don Wise

          HI Bishar,

           

          Change your Table Calc from COUNT to COUNTD and you'll get your 10.5%.

           

          Thanks, Don

           

          Screen Shot 2018-06-08 at 1.38.49 PM.png

          • 2. Re: Percents and counts
            Bishar Sethna

            Don,

             

            Thank you. that did the trick for my percentage. Any idea as to why my totals on some of the columns dod not come out correctly.

             

            Best,

            Bishar

            • 3. Re: Percents and counts
              Jim Dehner

              see the attached and below - you formula was based on count I changed it to countd and it returned

               

               

              it returns this

               

              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.

              • 4. Re: Percents and counts
                Bishar Sethna

                Jim,

                 

                Thank you.

                 

                That resolved my percentage issue. Any suggestion on why my total in the other columns do not come out correctly.

                 

                Best,

                Bishar

                • 5. Re: Percents and counts
                  Don Wise

                  Hi Bishar,

                  For either Jim or me to help, you'll need to provide some detail/context as to what's wrong, where, which totals -what's expected?  Thanks! Don

                  • 6. Re: Percents and counts
                    Bishar Sethna

                    Bob, Jim,

                     

                    First, I want to thank you both for taking the time to help.

                     

                    I have created different fields for Completed, Admitted, and enrolled as shown

                     

                     

                    I have tried Distinct, sum, and just count but my values in those columns do not come out correctly. for e.g., muy completed applicants for freshmen should show 5127 for fall 2018 and 5038 for fall 2017.

                     

                    Please let me know if that explains the problem.

                     

                    Thank you.

                     

                    Best,

                    Bishar

                    • 7. Re: Percents and counts
                      Don Wise

                      Hi Bishar,

                      Aside from Term, I am not seeing anything that can be used to uniquely identify the number of applicants and all of their transactions for the period of time involved.

                       

                      Is there some type of unique ID field that can be brought into the data to exclude it down further?

                       

                      For example, if I use your filter of APP STATUS and manually exclude "Incomplete Items Outstanding" (as per your calc), I get the following for FRESHMAN, which is approximately double of what you should be seeing as 5127 for Fall/2018 and 5038 for Fall/2017.  That explains why there's a need for COUNTD but I'm not seeing anything that can be uniquely used to make that work at this point...there are likely too many transactions within the data and nothing unique within the many data transactions to parse it down to the numbers you need to see...and...I just noticed that you're using data blending...so that's probably contributing to it...I'll keep looking at it.

                      Screen Shot 2018-06-08 at 2.22.11 PM.png

                      • 8. Re: Percents and counts
                        Bishar Sethna

                        Don,

                         

                        Again, thank you for taking the time.

                         

                        I may not be understanding what you are trying to say. I have a countd for my applicants and that comes out right. So I thought a countd for the other calculations should also show up. Correct me if I am wrong, and I will be in Tableau, but you mean I should have a calculation like Unique: countd(applicant_id). Then how ill I tie it to my other counts for completed, etc,

                         

                        Please bear with me as I am still learning Tableau.

                         

                        Best,

                        Bishar

                        • 9. Re: Percents and counts
                          Bishar Sethna

                          I think I see what you are trying to say. I the database I am using I have a field called proxy which is unique to each record. Again that may not be what you are talking about.

                          • 10. Re: Percents and counts
                            Don Wise

                            Hi Bishar,

                            I'm essentially reverse engineering your calculations.  Let's start with "Completed".  As you noted the numbers should be: completed applicants for freshmen should show 5127 for fall 2018 and 5038 for fall 2017.

                             

                            Is this then correct for the other Admit Types?

                             

                            1st Filter.png

                            • 11. Re: Percents and counts
                              Don Wise

                              Hi Bishar,

                              Please see newly attached workbook and disregard the above.  I pulled off your data as .csv and then reloaded into a new workbook.  The blending of the data is why I couldn't use LOD's (Level of Detail) calculations to get to the numbers you're expecting (rather what I think you're expecting as I don't have any validation on what they're supposed to be. 

                               

                              LOD's are not supported with alternate data sources (i.e., blended data).  As example, Tableau will error on any use of an LOD with blending:

                              Screen Shot 2018-06-08 at 3.40.59 PM.png

                              When I pulled out your data into a new workbook (no blending) I can then use LOD functionality to get you your numbers based partially on your original calculations:

                              Using Unduplicated.png

                              Why are LOD's needed here?  Because there are multiple transactions per PROXY ID.  Without PROXY ID (Unique ID), we wouldn't be able to get to the view you're expecting using LOD.  It explains why you were seeing odd numbers when using SUM, COUNT, COUNTD. And it explains why your calcs weren't working as Tableau was aggregating all transactions based on the view, regardless of the lower level of dimensionality (PROXY).  Notice that there are multiple transactions per PROXY ID:

                              Screen Shot 2018-06-08 at 5.20.49 PM.png

                              To avoid Data Blending, try joining your worksheets in the Data Pane window shown below. Click Add and then JOIN.  Also, you may want to check your joins when you do them as not everything is lining up (i.e., City, City Sheet 11, City Sheet 1, etc.):

                              Screen Shot 2018-06-08 at 5.23.46 PM.png

                              Hope this is correct for you!  Thx, Don

                              • 12. Re: Percents and counts
                                Bishar Sethna

                                Don,

                                 

                                Thank you for your explanation. the data blending was done when I posed a question on how to visualize grand totals of all admit types by semester. e.g,

                                 

                                Fresh f18   20

                                           f17  15

                                Oth    f18   5

                                          f17   6

                                Grtot  f18  25

                                           f17  21

                                 

                                instead of

                                 

                                Fresh f18   20

                                           f17  15

                                Oth    f18   5

                                          f17   6

                                Grtot        46

                                 

                                Hope what I said above makes sense.

                                 

                                The joins I do to match city and highschool city to their respective regions. Since I have one file with city - region and another highschool city - region I had those two joins. I may just need to join the regions in excel before bringing the file in Tableau

                                 

                                I looked at the solution in the workbook you attached. I am still confused as to why my Applicants, Completed, Admitted, and Enrolled counts are the same for the respective admit types. In the calculated fields I see where a condition needs to be met to be included in respective categories however the counts come out to be the same. Can you shed some light on this.

                                 

                                Again, Don, I tremendously appreciate your help. Your responses have an explanation tied to why or how to do something rather that just responding with a solution and this helps as I get to know more in Tableau. Thank you.

                                 

                                Best,

                                Bishar

                                • 13. Re: Percents and counts
                                  Don Wise

                                  Hi Bishar,

                                  I took a deeper dive this morning into the data that I used in the last workbook and the LOD's are producing the same results due to the data issues that are visible in the below.  Please see below screenshot, which should point you in the direction of joining the tables in the data pane (you don't have to do that in Excel outside of Tableau).  Add your primary data table in, then click Add in the upper left window of the Data Pane and add in the additional tables, data sources you need, but ensure the joins are correct so that the below doesn't occur with duplicate rows of data:

                                  Fall 2018 Enrolled Example.png

                                  So, the closest that I can get for you without a cleaner set of data is by going back to your blended data workbook and simply replacing "1" in your calculations with [Unique Count] which appears to be a COUNTD of [PROXY] from the blended table.  In this manner you would achieve the following results, again not knowing if these are correct or not, this is the best I can do for you.

                                  Screen Shot 2018-06-09 at 9.23.19 AM.png

                                  Thx! Don

                                  • 14. Re: Percents and counts
                                    Bishar Sethna

                                    Don,

                                     

                                    Thank you for your response. Thank you, more so, for taking time away on your weekend to help me.

                                     

                                    I am working with a new set of data which has actual Id's. I have eliminated all my joins and my totals seem to be coming out right. Before adding the other two sheets which give me high school region and county region I want to get my calculations right. Which brings me to question on how do I account for blank (null) values in my database.

                                     

                                    Below is  my calculation for "Admitted"

                                     

                                    IF [Latest Decision]="Application Withdrawn" THEN 0

                                    ELSEIF [Latest Decision]="Rejected" THEN 0

                                    ELSEIF [Latest Decision]="Rejected (Freshman Standards)" THEN 0

                                    ELSEIF [Latest Decision]=Null THEN 0

                                    ELSE 1

                                    END

                                     

                                    However the null values are being counted in the admitted total. I know I can reverse my calculation by assigning 1 to the admitted categories and finishing with 0.

                                     

                                    Another calculation I need help with is a nested one. e.g., only if the application is complete then look at that pool to assign a status of admitted. Hope I am making sense.

                                     

                                    Since the new database I am working with has identifying information I can't post it in this thread. However, if you want I can send it to your personal e-mail.

                                     

                                    Again thank you.

                                     

                                    Best,

                                    Bishar

                                    1 2 Previous Next