
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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....
- Tags:
- vb
- vb_macro_sc
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
please close this thread if your question is answered.
thanks
regards
Marco


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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....


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
please close this thread if your question is answered.
thanks
regards
Marco
