6 Replies Latest reply on Aug 21, 2018 12:59 PM by Ben Neville

# Sum [Score] only if score is > 0 in BOTH periods

Hi! I am facing a deadline and can't figure out how to filter (?) to ensure I'm getting accurate change statistics.

My data is sports outcomes for a group of students who were tested on ability at the beginning and then again at the end of the program. Some students were not present for both tests, some skills were not completed for each test. You can see this on Sheet 5.

I need to be able to sum a pre and post total for each student, but ONLY for those skills there were tested on in each period. Otherwise, I get false negative change statistics simply because more skills were tested in one period or another.

My calc is:

((IF [Sum Score Post]>0 THEN [Sum Score Post] END)

-

(IF [Sum Score Pre]>0 THEN [Sum Score Pre] END))

/

(IF [Sum Score Pre]>0 THEN [Sum Score Pre] END)

which does not return correct answers for what I've outlined above. Help!

Thank you.

Jessica

• ###### 1. Re: Sum [Score] only if score is > 0 in BOTH periods

What do you want to see?

• Only show Pre scores if there's a Post score, and vice-versa?
• Only show % Change if there are both scores available?
• Only count them in the total if there are both available?
• ###### 2. Re: Sum [Score] only if score is > 0 in BOTH periods

I'd like to be able to do both of the last two. Happy to have it happen on two different sheets.

• ###### 3. Re: Sum [Score] only if score is > 0 in BOTH periods

For the % Change Sum, you just need to change your calculation to:

IF ISNULL([Sum Score Pre]) OR ISNULL([Sum Score Post])

THEN NULL

ELSE ([Sum Score Post]-[Sum Score Pre])/[Sum Score Pre]

END

Assuming you only want the % Change Sum Total to be affected if there are missing pre/post scores, then this is what will happen normally anyway, as we don't calculate a percentage without a pre/post score. If you want to KEEP pre or post scores in the view even when one is missing, but EXCLUDE from the totals, that's trickier. In that case, we create a specialized calculation (may not work in other views if you're not splitting the metrics by Student) which tells Tableau to give us the values if there is only 1 student, but do something else if there are multiple students. Here's the method I came up with:

IF COUNTD([Student Name]) > 1 THEN

(SUM({INCLUDE [Student Name]: IF ISNULL([Sum Score Pre]) OR ISNULL([Sum Score Post])

THEN NULL ELSE [Sum Score Pre]

END})

)

ELSE

[Sum Score Pre]

END

That should return a value in the total if the pre score AND post score exist, but will display in the view regardless of whether they both exist.

• ###### 4. Re: Sum [Score] only if score is > 0 in BOTH periods

This works a treat! Thank you so much!

• ###### 5. Re: Sum [Score] only if score is > 0 in BOTH periods

Actually, I also need to be able to calculate a total score for each student, pre and post, where the sum for each period is the sum of only skills where both exist in each period. Would that just be:

IF ISNULL([Sum Score Pre]) OR ISNULL([Sum Score Post])

THEN NULL

ELSE [Sum Score Post]

END

?

• ###### 6. Re: Sum [Score] only if score is > 0 in BOTH periods

You got it... that'd return the Post score under the conditions described.