Need Expression Assistance (Please)

jcampbell474 — Fri, 18 Aug 2017 17:33:20 GMT

I need to ask for some help with an expression. I think it’s ‘almost’ where I need it. Just can’t figure out the last piece.

Context:

First, on any given day, telephone numbers call in. User selects a day and QV counts the number of times the telephone called. If it called one time only, it gets a value of 1. If it called >1 times, it gets a value of 0. The sum of this column divided by the total distinct count of telephone numbers provides a First Call Resolution %.

Next, the user can select a trailing date range. In the example below, vdatekey is the user selected date. vTmaxdate is set using a listbox (T1-T30) (T = ‘Trailing # Days’). The variable is set using the Date(vdatekey – GetSelectedCount). The datakey is the call’s workgroup and telephone number. There are 30 expressions in the chart object, T1-T30. Each one with vdatekey – 1, vdatekey -2, etc… It tells me what I need to know using the expression below, but it doesn’t give me a sum (Total Mode > Expression). I need the sum so that I can divide it by the total count of distinct telephone numbers on the date selected by the user.

Any ideas on how I can get this to total?

=if(aggr(only({<datekey={"$(=Date(vdatekey))"}>}datakey),datakey) =
aggr(only({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>}datakey),datakey),
aggr(sum({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>}Ct),datakey)=1,0)*-1

This expression is in the FCR% column…the one I need to Total.

Re: Need Expression Assistance (Please)

sunny_talwar — Fri, 18 Aug 2017 17:39:47 GMT

May be this

Sum(if(aggr(only({<datekey={"$(=Date(vdatekey))"}>}datakey),datakey) =
aggr(only({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>}datakey),datakey),
aggr(sum({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>}Ct),datakey)=1,0)*-1)

or this

Sum(Aggr(If(Only({<datekey = {"$(=Date(vdatekey))"}>} datakey) = only({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>} datakey), sum({<datekey={">=$(vTmaxdate) <=$(vdatekey)"}>}Ct) = 1, 0) * -1, datakey))

Re: Need Expression Assistance (Please)

jcampbell474 — Fri, 18 Aug 2017 17:57:07 GMT

Wow, that was fast! The 2nd expression worked. Again, thank you for your help, Sunny!

topic Need Expression Assistance (Please) in QlikView

Need Expression Assistance (Please)

Re: Need Expression Assistance (Please)

Re: Need Expression Assistance (Please)