topic Re: Help with expression in QlikView

Help with expression

isciberras — Tue, 21 Nov 2017 11:51:27 GMT

Hi,

I am currently in the process of creating a password analysis dashboard and I have a question with regards to creating an expression which I could use to quantify users and their failed login attempts.

I managed to get the answer I need from a chart I created (I attached pictures of the chart), however I am finding it difficult to write an expression which will produce the same result.

Basically I am trying to quantify the amount of users which had 2 failed loagin attempts more than 3 times. The expression in my graph is COUNT(Failed_Attempts) and my dimension is Failed attempts, USER and DATE [I am using a drill down group]

Does anyone have an idea if I can create an expression to do this? So far I tried =aggr(Count(Failed_Attempts),TRAN_TYPE,USR) but this does not give me any result.

Thanks,

Isaac

(The attached image(001) shows the range of failed login attempts. image004 shows the number of times a user got 2 failed login attempts)

Re: Help with expression

Anil_Babu_Samineni — Tue, 21 Nov 2017 11:53:32 GMT

Can you use Sum(aggr(Count(Failed_Attempts),TRAN_TYPE,USR))

Re: Help with expression

isciberras — Tue, 21 Nov 2017 13:15:10 GMT

Hi Babu,

Thank you for your help, with your input I managed to refine the expression to get a correct answer. I now achieved the answer of the first graph I attached.

=sum(aggr(Count({$<[Failed Attempt]={'2'} >}[Failed Attempt]),TRAN_TYPE,USR))

The only issue with the result of this expression is that it brings back the count of all the failed logins with a second failed attempt even if they only had a failed login second attempt on one occurrence. I would now like to refine the syntax further to bring back only the count of failed attempts where the user had a second failed login on more than 3 occurrences.

Really appreciate your input on this.

Thanks,

Isaac

Re: Help with expression

sunny_talwar — Tue, 21 Nov 2017 15:15:19 GMT

May be this

=Sum(Aggr(If(Count({$<[Failed Attempt]={'2'}>}[Failed Attempt]) > 3, 1, 0),TRAN_TYPE,USR))

Re: Help with expression

isciberras — Tue, 21 Nov 2017 15:31:49 GMT

You're a genius, code did exactly what I needed it to thank you and I understood the logic, thank you