Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
MarcoWedel

please close this thread if your question is answered.

thanks

regards

Marco

View solution in original post

4 Replies
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_143754_Pic1.JPG.jpg

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

MarcoWedel

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

Not applicable
Author

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

MarcoWedel

please close this thread if your question is answered.

thanks

regards

Marco