count or sum IF sum is greater than

[DD-44]

Hi everyone,

I'm  new to scripts and set analysis and I'm struggling to find a solution to the following problem:

Our staff gets tested for phishing 3 or 4 times a year (different regions in different months).  We have been doing these tests since 2017, meaning people have been tested at least 10 times.

I would like to create a formula to show in a chart and table when a user has failed more than 2 times or more 3 times, etc. My problem is that I use a field (Test_Fail = 1), when the user has failed each specific test.

What I tryed and isn't working:

Count(If(sum(Test_fail) > 1, 1, 0))  

and also: Count({$<Sum(Test_fail={">=$(=2)"})>}distinct Email)

but it isn't working.

Relevant fields:

LOAD
Email,
Date([Sent]) as [Date_Sent],
"Test Name",
Country,
"Region Name",
"Dep_Name",
"Employee Status",
Region,
[Test_fail]

Resuming, I need to show the count of users that have failed a more than a specific number of times.

Also, is there a way to only for the last 12 months?

Thanks in advance.

Dan

[lblumenfeld · Accepted solution]

Here is how to do this...

I loaded the following data:

Data:
Load * Inline [
Region, Email, Date_Sent, Test Name, Test_fail
X, a@b.com, 8/1/2019, T1, 0
X, a@b.com, 10/1/2019, T2, 1
X, a@b.com, 2/1/2020, T3, 1
X, a@b.com, 3/1/2020, T2, 1
X, a@b.com, 4/1/2020, T1, 0
X, a@b.com, 4/5/2020, T2, 1
X, x@y.com, 4/5/2019, T2, 1
X, x@y.com, 2/5/2020, T3, 0
X, x@y.com, 2/6/2020, T1, 1
X, x@y.com, 2/15/2020, T1, 1
X, x@y.com, 3/5/2020, T2, 1
X, x@y.com, 4/5/2020, T1, 1
X, x@y.com, 4/7/2020, T2, 1
X, p@q.com, 9/10/2019, T1, 0
X, p@q.com, 10/10/2019, T2, 0
X, p@q.com, 1/10/2020, T3, 0
X, p@q.com, 2/10/2020, T2, 0
X, p@q.com, 4/10/2020, T1, 0
];

User a@b.com  failed test T1 two times in the past 12 months.
User a@b.com  failed test T2 three times in the past 12 months.
User x@y.com  failed test T1 two times in the past 12 months.
User x@y.com  failed test T2 three times, but only two times in the past 12 months.

We want to know how many users failed each test more than 2 times in the last 12 months from today's date. So, we should get a count of 1 for T1 and 1 for T2.

See the sample table.

The formula for the column "Users Failed >=2 Times In Last 12 Months" is

=Sum(Aggr(If(Sum({< Date_Sent={">$(=AddMonths(Today(), -12))"} >} Test_fail) >= 2, 1, 0), [Test Name], Email))

You can change the ">= 2" to whatever failure count threshold you'd like, of use a variable to allow the user to change it.

I hope this helps.

[DD-44]

Thank you! I worked pretty well.