Hi everyone. Can anyone please tell me how to filter rows in the table with a condition , to retrieve only past 3 months where there was a 'fail' criteria occurring consequently month after month.?

[Keerthana Lagisetty]

Also, if someone could help out with the condition where there was a fail criteria in any of the 3 consecutive months. Any help would be appreciated. Thank you.

[Gaia Paolini]

Could you be more specific, and share a sample dataset that reproduces the conditions?

For instance,

• Is there any grouping column?
• Can there be e.g. 3 consecutive fails then a pass, then more fails?
• Do you need to filter on a row where there were only fails or any fail in the previous 3 months?
• Are all months represented, or are there months that are skipped?
• Is there one row per month?

[Keerthana Lagisetty]

​

This is the sample data. And there are pass fields after fail ones.

I want to filter the rows where there fails consecutively in the last 3 months. If there is a failure in June, July, August then that should be kept.

There are some facilities with all months represented and there are some which do not have. Also, there is one row per month.

Please let me know if you need any more information. Thank you.

​

[Gaia Paolini]

Thank you.

Would the grouping column be FACILITY_NAME + INSPECTOR + INSPECTION_TYPE? Or is it just the FACILITY_NAME that matters?

When you say 'that should be kept' are you saying that A: only August should be kept, or B: that June, July and August should be kept?

In the facilities where not all months are reported, then we would not know whether it was a pass or a fail in the missing months, right? All we know is that there are/are not 3 consecutively reported fails.

Please can you indicate which row numbers you would want to keep from your example.

[Keerthana Lagisetty]

The grouping columns would be FACILITY_NAME + INSPECTION_DATE+ SEE. The main criteria is to see how many facilities failed in the last three months on and on from today ().

I have around 7000 rows of data, in which there are some facilities having been Failed from the last couple of months. The example data is just a subset of the whole dataset.

For example., in the above given sample data ... if today is ACD facility failed say the month of march. And I want to calculate the consistent failures from the last three months. Then, after using the condition, I should be able to see only the rows - 8,9,10. And rest of the data should be limited.

​

​

In the facilities where not all months are reported, then we would not know whether it was a pass or a failure in the missing months, right? All we know is that there are/are not 3 consecutively reported fails. - Absolutely true. We would not. But 3 is just a number I picked. I could modify the 3 to 2 or any other number later.

Thank you.

[Gaia Paolini]

OK so when you say past 3 months you meant from today, not any consecutive 3 months.

Try this: (I used different columns and fail values but should be straightforward)

case when ((Month(DateTimeNow()) - Month([Date]))<=3) and (Year([Date])=Year(DateTimeNow())) then (Count(Integer([Flag]='FAIL')) over ([Group])>=3) and ([Flag]='FAIL')else Falseend

Where:

(Month(DateTimeNow()) - Month([Date]))<=3) and (Year([Date])=Year(DateTimeNow())

tells me we are within 3 months and same year as today. Today is DateTimeNow()

Your [Date] column needs to be of type Date or DateTime in Spotfire.

Integer([Flag]='FAIL')

is 0 when not fail (False) and 1 when fail (True).

over([Group])

uses my grouping column(s) to restart the count at each new group. 

Note that [Date] cannot be a group column as you are counting on it.

This column should flag every row that had 3 failures in the past 3 months.