topic Re: Syntax for a boolean function in QlikView

Syntax for a boolean function

Thu, 17 Jan 2013 19:22:18 GMT

I want to create a VBS function that returns true or false if a date is between StartDate and EndDate. (Just to keep the formulas more compact and readable)

I wrote:

FUNCTION twixt(Target,StartDate,EndDate)

IF Target > StartDate AND Target < EndDate THEN

twixt = TRUE

ELSE

twixt = FALSE

END IF

END FUNCTION

However, this doesn't seem to return either TRUE or FALSE, and when I use it other formulas, such as

IF(twixt(BCD,StDate,EDate),sum(Sales),0)

I just get NULLs as a result.

As usual, there is nothing in the documentation about the method of creating functions. Do I need to declare the function as LOGICAL, BOOLEAN, etc.?

What should I expect as a return value? TRUE/FALSE, or -1/0?

Thanks for any help.

Re: Syntax for a boolean function

fosuzuki — Thu, 17 Jan 2013 19:50:31 GMT

Have you tried using dollar-sign expansion with parameters?

Check the attached qvw.

The idea is to use a variable

fTwixt = if($1>$2 and $1<$3, 1, 0)

and the expression will be:

=$(fTwixt(Target, StartDate, EndDate))

Hope this helps

Fernando

Re: Syntax for a boolean function

Thu, 17 Jan 2013 20:10:11 GMT

Hi Kevin,

in QlikView you can define Procedures by the keyword SUB

but there are no user defined functions like you tried. They are on the wishlist for some time.

Simple functions can be done with variables as Fernando showed above.

Ciao

Klaus

Re: Syntax for a boolean function

sebastiandperei — Thu, 17 Jan 2013 20:46:14 GMT

Hi Kevin

When you assign True value to a variable on VBScript, this var will get the '-1' value. If you assign False, the value is '0'.

Try with this:

IF(twixt(BCD,StDate,EDate)=-1,sum(Sales),0)

Re: Syntax for a boolean function

Thu, 17 Jan 2013 21:07:05 GMT

Sorry Kevin,

you were talking about VBS Fernando and me we were in a QV script.

Ciao

Klaus