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

# Count  records for particular year

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).

Thanks

Ashok

• ###### 1. Re: Count  records for particular year

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

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

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

• ###### 4. Re: Count  records for particular year

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

Hi Ashok,

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