Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thank you! I worked pretty well.