5 Replies Latest reply on Mar 8, 2013 1:16 AM by Dana Withers

    Count  records for particular year

    Ashok rav

      Hi,

       

      I am new to tableau, we have scenario where we need to calculate the rates og graduation by satifying the below logical condition.

       

      COUNT([Time_To_Degree]<=4  AND [Cohort_Year]=2004)/COUNT([Time_To_Degree]>=4 AND [Cohort_Year]=2004).

       

      I used the same logic in tableau and all I am getting are 1 or 100%(when i change the number format).

       

      The same logic applies good on excel sheet. Can someone please help me out here.

       

      Thanks

      Ashok

        • 1. Re: Count  records for particular year
          Dana Withers

          Hi Ashok,

           

          I'm a bit confused by your logical condition - those with a time to degree of 4 count on both sides of the calculation.

           

          However I had a play... a calculation is done for each row, which I think is not what you intend, also count counts how many values there are and the reason you get a 1 is it counts if there is an answer to your logic, not if the logic actually applies (both true and false are answers and therefore "count" equally).

          I think you should use an aggregate of some sort and probably a sum. I've tried to simplify the calculation so it is easier to track. I've created two calculated fields that simply give a 1 if the logical condition is met or a 0 if it is not. The logical condition is purely based on the time to degree. This way I get two groups - one for time to degree of 4 or less and one for more than 4 and I can "count" how many are in each group by using Sum. Now I can create the graduation rate by dividing the sum of the first group by the sum of the second group. If you have years in your column, the calculation is automatically done per year, so you don't need to calculate for each year manually.

           

          I hope this helps,

           

          Dana

          • 2. Re: Count  records for particular year
            Ashok rav

            Hi Dana,

             

            Your way made me think to implement the logic in correct way.I know my logic implies to excel sheet and i didnt know how to implement that in Tableau.

            This is very useful tip you gave me . I really appreciate your help.

             

            Thanks

            Ashok

            • 3. Re: Count  records for particular year
              Ashok rav

              Hi Dana,

               

               

              with the logic you seggested we are getting Grade ratee more than 100% which is not true when i validated the data in the excel sheet.

               

              I need to implement a logic which is like below

               

              Gradrate= number of UID's where "cohort_year=2004 AND  Time_To_Degree<= 4)  / ( number of UID's where cohort_year=2004)

               

               

              So in the denominator I cannot just use Time to degree more 4

              Can you Please help me

              • 4. Re: Count  records for particular year
                Ashok rav

                Hi Dana,

                 

                Thanks for the help,It worked with a slight change in the logic

                 

                Grad Rate= SUM([Time to degree less 4])/SUM(([Time to degree more 4])+[Time to degree less 4])

                 

                Thanks

                Ashok

                • 5. Re: Count  records for particular year
                  Dana Withers

                  Hi Ashok,

                   

                  Glad it helped!

                  Another way to get the total number of rows in a group is the default "Number of Records" field.

                  SUM([Time to degree less 4])/ sum([Number of Records])

                   

                  Does all of that fully answer your question or was there anything outstanding ?

                   

                  Kind regards,

                   

                  Dana