topic Need urgent help: Want to use an Excel Function in Qlik View in QlikView

Need urgent help: Want to use an Excel Function in Qlik View

Wed, 26 Nov 2014 21:40:12 GMT

Need urgent help as don't know much about VB Script... Want to use the attached Script/Function in QlikView....

Re: Need urgent help: Want to use an Excel Function in Qlik View

MarcoWedel — Wed, 26 Nov 2014 22:23:25 GMT

Hi,

one solution could be:

Using this module:

Public Function Utilisation(Agents, CallsPerHour, AHT)
'Calculate the utilisation percentage for the given number of agents
' Agents is the number of agents available
' CallsPerHour is the number of calls received in one hour period
' AHT (Average handle time) is the call duration including after call work in seconds  e.g 180

     BirthRate = CallsPerHour
     DeathRate = 3600 / AHT
'calculate the traffic intensity
     TrafficRate = BirthRate / DeathRate
     Util = TrafficRate / Agents

     Utilisation = MinMax(Util, 0, 1)
End Function

Function Max(a,b) 
    Max = a 
    If b > a then Max = b 
End Function

Function Min(a,b) 
    Min = a 
    If b < a then Min = b
End Function

Function MinMax(a,b,c) 
    MinMax=Min(Max(a,b),C) 
End Function

and this test data script calling the Utilisation function:

table1:
LOAD *,
     Num(Utilisation(Agents, CallsPerHour ,AHT*86400),'##0.0%') as Utilisation;
LOAD Ceil(Rand()*30) as Agents,
     Ceil(Rand()*1000) as CallsPerHour,
     Time(Ceil(Rand()*300)/86400) as AHT
AutoGenerate 40;

hope this helps

regards

Marco

Re: Re: Need urgent help: Want to use an Excel Function in Qlik View

MarcoWedel — Wed, 26 Nov 2014 23:26:25 GMT

in terms of performance it surely is better (and shorter) to implement this function in the QlikView script instead:

SET vUtilisation = RangeMin(RangeMax($2*$3/(3600*$1),0),1); 

table1:
LOAD *,
    //Num(Utilisation(Agents, CallsPerHour ,AHT*86400),'##0.0%') as UtilisationVBS,
    Num($(vUtilisation(Agents, CallsPerHour ,AHT*86400)),'##0.0%') as UtilisationQVS;
LOAD Ceil(Rand()*30) as Agents,
    Ceil(Rand()*1000) as CallsPerHour,
    Time(Ceil(Rand()*300)/86400) as AHT
AutoGenerate 40;

A quick run time comparison delivered 26sec for the VBS solution against 6sec for the QV script implementation ...

hope this helps

regards

Marco

Re: Re: Need urgent help: Want to use an Excel Function in Qlik View

Thu, 27 Nov 2014 04:09:33 GMT

Thanks you so much Dear Marco for the resolution and quick response.... Will try and let you know in case need any further help.... I have some more issues around this .... Looking forward for your kind support.... thanks once again....

Re: Need urgent help: Want to use an Excel Function in Qlik View

MarcoWedel — Thu, 27 Nov 2014 17:02:32 GMT

please close this thread if your question is answered.

thanks

regards

Marco