在Spotfire中使用Over函数时如何排除空值并实现指定日期逻辑

Let's break down why your current formula isn't working:

• You're using Min([Original End Date]) over [Student], which only checks against the earliest end date instead of all non-null end dates for the student.
• Null values in Original End Date aren't being handled properly, so they're throwing off the validation logic.

Correct Calculation Field Code #

You'll need to use a combination of window functions and conditional checks to validate all non-null rows per student, then apply that result to every row for the student. Here's the working formula:

Case
  When (Count(Case When [Original End Date] Is Not Null And DateAdd('dd',1, [Original End Date]) != [Start Date] Then 1 End) over ([Student])) = 0
  Then TRUE
  Else FALSE
End

How This Works #

Let's break down the logic step by step:

• Inner Case Statement: For each row with a non-null Original End Date, it checks if Original End Date + 1 day does NOT equal Start Date. If this is true, it returns 1 (marking a failed validation).
• Count Over Student: This counts how many failed validation rows exist for the entire student group.
• Outer Case Statement: If the count of failed rows is 0 (meaning all non-null rows passed the validation), every row for that student gets TRUE. Otherwise, all rows get FALSE.

Example Breakdown #

• For Student A: All non-null rows pass the date check, so the count of failed rows is 0 → all rows return TRUE.
• For Student B: There are rows where the date check fails (e.g., 2/1/2018 → 3/1/2018 is not +1 day), so the count is greater than 0 → all rows return FALSE.
• For Student C: All non-null rows pass → all rows return TRUE.

This formula properly handles null values in Original End Date (since those rows don't contribute to the failed count) and ensures consistent TRUE/FALSE results across all rows for each student.

内容的提问来源于stack exchange，提问作者Evie