1 2 Previous Next 18 Replies Latest reply on Mar 25, 2019 4:02 AM by Rachel Nichols

# Calculation help

I am trying to create a viz representing the percent of students who have one or more Fs for a specific time period. There are multiple rows for each student, representing different courses. Some students take one course; others may take up to 8. How can I display the percent of students who have at least one F? I'm sure it's a calculation.

• ###### 1. Re: Calculation help

Hello Rachel,

You can use this calculation to count the students with at least 1 F.

{ FIXED [H04 - State Student ID (SSID]: max(if [H10 - Letter Grade] = "F" then 1 end)}

see attached. Hope this helps!

Robert

• ###### 2. Re: Calculation help

Thank You, Robert! That was helpful.

Now I need another step (which I should have included in my initial email …)

Can I adjust the calculation to include students without any Fs? So I would see a graph that has those passing all classes and those with at least one F?

Rachel

Rachel Nichols

• ###### 3. Re: Calculation help

You can use this LOD

{FIXED [H04 - State Student ID (SSID] : SUM(IF [H10 - Letter Grade] = "F" THEN 1 ELSE 0 END) > 0 }

With this expression, you give each row in your dataset a 1 when the letter grade is an F, and then SUM it per student. The expression will return True, when there are more than 0 records per student, and thus will indicate whether that student had an F

• ###### 4. Re: Calculation help

YES! Thanks so much!!

Rachel

Rachel Nichols

• ###### 5. Re: Calculation help

like this?

• ###### 6. Re: Calculation help

May I ask a follow-up question?

Now I’m trying to create a similar viz but with different data. I’m getting the same data in both S1 and S2 (semesters 1 & 2). I’m guessing perhaps the problem is in the Tableau Prep file, but I don’t know what it is. (still learning Tableau Prep Builder).

Rachel

Rachel Nichols

Assessment Department

North Thurston Public Schools

360-412-4461

• ###### 7. Re: Calculation help

In the expression, it doesn't include the semester as a LOD (Level Of Detail). You can fix this by either changing the FIXED to INCLUDE, or by adding the Semester to the FIXED dimensions.

• ###### 8. Re: Calculation help

Rachel,

You've had some good responses, and I have one more for you - similar to the others, but a slightly different approach:

1.  Create a calculated field to determine the number of students with at least one failing grade:

2, Create a calculated field to determine the number of students who don't have any failing grades (which is just the total number of students less the number that have at least one failing grade):

And then create a viz with "H19 - Term" on the Columns Shelf, and "Measure Values" on the Rows shelf (and place "Measure Names" on the Filter shelf, so you can limit the measures that are included in the graph, to just those you want to see).  Here's how my viz looks:

Hope that helps.

Kaz.

• ###### 9. Re: Calculation help

That helps until I add a filter for grades (grade level)—then I get that both terms have the same results.

Rachel

• ###### 10. Re: Calculation help

I’m still struggling with LOD. How can I fix that?

Do you mean simply replacing “INCLUDE” for “FIXED”?

How would I add Semester to FIXED?

Rachel

• ###### 11. Re: Calculation help

Rachel,

I think if you just add the Grade Leevl field to the FIXED in each of the formulas, it should work.  In the original workbook you posted, I don't see a Grade Level field, so here's an example using School Code instead:

Does that get to the results you were looking for?

Kaz.

• ###### 12. Re: Calculation help

Ah … I’m working on a slightly different data set, and I think the problem is with the data set, not the viz.

Rachel

• ###### 13. Re: Calculation help

If you want to add the semester, then add it to the list of dimensions you always want to include in your Level Of Detail (LOD), such as

{FIXED [H04 - State Student ID (SSID], [H19 - Term] : SUM(IF [H10 - Letter Grade] = "F" THEN 1 ELSE 0 END) > 0 }

• ###### 14. Re: Calculation help

Yes! That works!!

(now to figure out WHY is works, and what magic you are using in your formula ….)

Thanks much,

Rachel

1 2 Previous Next