VBScript question

[jduenyas]

Hi All

I have created a function in the Edit Module in VBScript which given 2 dates return the elapsed time in Days,Hours, Mins, taking into account work hours (8AM - 6PM), weekends and holidays. So if an employee started a job at 4/6/2012 9:03:00 and fiinished 4/12/2011 16:60:00, the function will evaluate that to  5 Days, 7 Hours, 27 Minutes.

The function works correctly when running it from within the Edit Module however it does not when I try to deploy it from within an expression.

In the expression I pass to the function (TimeCard) the following:

=TimeCard(Date1, Date2) where Date1 and Date2 are fields in one of the tables which I read from our database.

Am I doing anything wrong that no values are returned?

Thanks

Josh

[#2]

Hi,

When you say in the expression, you mean that you are using the function inside Edit Module only, right?

Can you post your application?

Also, this type of calculation you can do in native qlikview itself, variables may come handy here. I don't see need of a macro.

You should not use macros extensively, they themselves are slow in execution as well as make whole application itself slow.

..

Ashutosh

[jduenyas]

Hi Ashutosh,

Sorry i am so late with my reply to you. I got haung up with other burning issues in our company.

The attached is a sample QV and data and a sample VBScript function which is called from the expression but does not give the results I am looking for.

Any input will be appreciated.

Thanks

[s_uhlig]

Hi Josh,

it's not possible to use vbscript functions in an expression. However you can do the calculation during load in the script.

load DATESTART,DATEEND,MultiplyByFive(MO) as MO5; ...

Regards

Sven

[#5]

Hi jduenyas,

Sorry for late reply.

You can not call the functions/subroutines defined inside macro section in the expressions of an object. That subroutine can be executed in macro section only.

But if you want a custom function then you can use variables as a function.

Also, for your requirement, you don't need to use a function you can directly do it inside an expression without using any function.

Please find the attached applicaiton to see examples for both of the cases.

Let me know, if any issue.

..

Ashutosh

[jduenyas]

Sven

thank you for you reply. However your suggestion did not work and I cannot understand why.

The attached is the real function which I am trying to use with some data.

Any suggestion is appreciated.

Thank you

Josh

[jduenyas]

Once again thank you Ashutosh.

I do not think I can use your suggestion as the function I showed initially was much more simplified than the one I am using.

Attached you will find the "real" function. I would not know how to embed that into a variable as you suggested.

If you have any suggestions or can enlighten me, I will appreciate it.

Thanks

Josh

[s_uhlig]

Hi Josh,

give

TimeCard(''&EmailReceivedOn,''&QuotedOn) AS ElapsedTime

a try, which will force the string representation of your dates, otherwise they will be just numbers.

Regards

Sven

[#9 · Accepted solution]

Hi jduenyas,

You don't need to put this function in a variable.

It can work fine from macro itself.

The problem why it was not workin in your case was because of date format not being properly parsed into vbscript.

I have corrected it.

I have changed the variables where you assign dtDate1 and dtDate2.

Please find the attached application, let me know if any issue.

..

Ashutosh

[jduenyas]

You are the BEST!!!