Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

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

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;

MonthsCall DateFull NameGroupFail1Fail1CommentFail2Fail2CommentRegulatory_Pass
101/01/2016Joe BloggsMike's GroupReason1Comment10
109/01/2016Paul SmithPete's GroupReason1Comment10
217/02/2016Joe BloggsMike's Group1
2Paul SmithPete's Group1
3Joe BloggsMike's Group1
3Paul SmithPete's Group1
4Joe BloggsMike's Group1
4Paul SmithPete's Group1
5Joe BloggsMike's GroupReason1Comment10
5Paul SmithPete's Group1
6Joe BloggsMike's Group1
6Paul SmithPete's GroupReason1Comment10
7Joe BloggsMike's Group1
7Paul SmithPete's Group1

And the resulting table in Qlikview would look as so;

PositionAgentNo. of Passes
1Joe Bloggs2
2Paul Smith1

Hope this helps!

Thanks

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

1 Solution

Accepted Solutions
MVP
MVP

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

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))

Capture.PNG

7 Replies

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

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

MVP
MVP

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

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.

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

Try this:

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


Capture.PNG

MVP
MVP

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

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))

Capture.PNG

Not applicable

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

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?

MVP
MVP

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

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;

Not applicable

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

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