Rolling count of number "1" since last "0"

[#1]

Hello all,

This is my first post so I apologise in advance if it isn't clear.

Our business would like to create a league table based on the number of passed calls an agent has had since they last failed a call. So the larger the number of calls since they've last failed, the higher up the league they'll be.

Dimensions:

Agent name is "TMRName"

Expressions:

To count the number of assessments, I use "count(CallDate)".

To count the number of passed assessments, I use "sum(Regulatory_Pass)".

The field "Regulatory_Pass" has a "1" in it if the agent has passed the call, if "0", then they have failed.

I need a formula that will count the number of passed assessments that the agent has last passed a call, and that will reset if an agent has failed.

Please can someone help?

Thanks

__________________________________________________________________________________

UPDATE:

A mock table is as below;

Months	Call Date	Full Name	Group	Fail1	Fail1Comment	Fail2	Fail2Comment	Regulatory_Pass
1	01/01/2016	Joe Bloggs	Mike's Group	Reason1	Comment1			0
1	09/01/2016	Paul Smith	Pete's Group	Reason1	Comment1			0
2	17/02/2016	Joe Bloggs	Mike's Group					1
2		Paul Smith	Pete's Group					1
3		Joe Bloggs	Mike's Group					1
3		Paul Smith	Pete's Group					1
4		Joe Bloggs	Mike's Group					1
4		Paul Smith	Pete's Group					1
5		Joe Bloggs	Mike's Group	Reason1	Comment1			0
5		Paul Smith	Pete's Group					1
6		Joe Bloggs	Mike's Group					1
6		Paul Smith	Pete's Group	Reason1	Comment1			0
7		Joe Bloggs	Mike's Group					1
7		Paul Smith	Pete's Group					1

And the resulting table in Qlikview would look as so;

Position	Agent	No. of Passes
1	Joe Bloggs	2
2	Paul Smith	1

Hope this helps!

Thanks

Message was edited by: James Robertson 19/10/2016

[sunny_talwar]

Would you be able to share some raw data with expected output to help you better?

[johnw]

I assume we have some sort of CallTimestamp as well. If so, maybe something like this?

count({1<CallTimestamp={"=CallTimestamp>max({1<Regulatory_Pass={'0'}>} CallTimestamp)"}>} CallTimestamp)

edit: Fail. Outer set analysis not in context of dimension. Also fails to count if person never got a 0.

[sunny_talwar]

Try this:

=Sum({<Regulatory_Pass = {1}>}Aggr(If(Months >= Max(TOTAL <[Full Name]> {<Regulatory_Pass = {0}>}Months), 1), [Full Name], Months))

[johnw · Accepted solution]

I think that regardless of what the sample data says, people can probably have multiple calls a day, let alone per month. Thus the need to use a timestamp. Of course it should be trivial to replace months with timestamps in your expression, and I did so in the attached app, though it also fails if there are only 1s.

Anyway, here's what I came up with using random data and timestamps and fixing the "if we only have 1s" bug we both had. It seems like there should be a simpler way. Well, if we're going to ignore all selections as I do here, we could just load it in the script I guess.

if(count({1<Regulatory_Pass={0}>} CallTimestamp)

,-sum({1} aggr(CallTimestamp>max({1<Regulatory_Pass={0}>} total <FullName> CallTimestamp),FullName,CallTimestamp))

,count({1} CallTimestamp))

[#6]

Mr Witherspoon, thank you for this! It has worked perfectly.

One other thing:

Now I'm looking through data dating back to 2015. People have since left the business. Is there any way that I could tag onto this query, a way in which that it'll only present agent's names if they've had an assessment in the last 3 months?

[johnw]

I'm starting to think I'd do everything in script. I was using {1} in my expressions anyway, which meant they weren't sensitive to selections, and so there's not much harm in doing the aggregation in script, even though that's normally something I'm vehemently opposed to. In this case, the "pass streak" can just be considered a fact, not something that has anything to do with any selections.

I feel like there might be a simpler way, but I'm not sure how to achieve nested aggregation, so I ended up doing two resident loads where I wanted one. Anyway, here's the script that sets us up to include only people with recent assessments, and to count the pass streak for each person.

LEFT JOIN (Main)
LOAD
FullName
,alt(max(if(Regulatory_Pass=0,CallTimestamp)),makedate(2000)) as MostRecentFail
RESIDENT Main
GROUP BY FullName
;
People:
LOAD
FullName
,if(max(CallTimestamp)>=monthstart(today(),-3),FullName) as ActiveName
,count(if(CallTimestamp>MostRecentFail,CallTimestamp)) as PassStreak
RESIDENT Main
GROUP BY FullName
;
DROP FIELD MostRecentFail;

[#8]

Hi John,

Hope you're well.

I'm now using the coding for reporting in our department, but agents have come to me explaining that the count is in fact wrong. There are agents with 20 passes in a row, but the report is detailing that there are 17.

I thought there might have been an issue with the fact that we are now in a new year, so there is another excel document to incorporate, but it seems that the coding is picking up some/all of these passed assessments.

Please can you assist? If you need anything, please let me know.

Thanks,

James