User Defined Function (UDF) using Variables

[IAMDV]

Dear All,

I am trying to use a UDF to calculate the % diff between two variables. And I wanted to create a UDF which acts as expression and accepts two input variables. I had written the same function in Excel VBA for your understanding.

Public Function PercentageDiff(vThisYear As Integer, vPreviousYear As Integer)
PercentageDiff = (ThisYear / PreviousYear) * 100 - 100
End Function

Please help on this one. Thanks in anticipation.

Cheers - DV

[IAMDV]

Hi Tim,

Awesome! This technique is opening more possiblities in QV. I had checked your blog and it is fantastic! Many thanks again.

I got it working now.

Cheenu - I hope you are following this one... Cool technique from Tim.

Cheers - DV

[IAMDV]

Hi Tim,

Very quick question... What is the performance implication? I am assuming that it evaluates the variables everytime the chart is called.

Please can you share or guide to an article to demostrate best practices on using variables & expressions.

Many thanks again.

Cheers - DV

[#4]

Hi Deepak,

I initially thought it might be with passing of a parameter in a var, but didn't know how to figure it out. It's still the darker side of QV expression that I haven't fully come to grips with.

Thanks for keeping me updated. I'm going to copy/paste and re-read that blog post a few times.

FYI as another technique: Leveraging HelpInfo text with passing of parameter and using AGGR function (if you might need it some time).

The one place where I used passing of parameter is in regard to using the HelpText info on chart objects (Caption tab).

If you create a table load with 2 columns: HelpID and HelpText, you can re-use these with a variable on multiple objects or multiple apps (QVWs).

Load the table (with 2 columns as above) with the info that you want to include in the HelpText of objects (the question mark button in Caption bar), then in front-end create a var such as vHelpText=aggr(HelpText, HelpID = $1). Then, whenever you want to call for one of the HelpText cell value you have on the table you loaded, in HelpText put "=$(=vHelpText(N))", where N is the ID number for which you want to return the associated HelpText. You can do a string of concatenations of Help Text, e.g. =$(=vHelpText(1)) & '; ' & =$(=vHelpText(2)).... etc. (Zip file attached containing example QVW & Excel source data table).

I know passing parameter in expression is VERY powerful, but all of this is just hitting the tip of the iceberg.

Good that someone was able to help you out. Thanks for the heads up on the blog from Tim Benoit.

Hope if you get your head around passing of parameters that you'll help me out on it one day. ;o)

[#5]

Hi Tim Benoit,

Thanks for sharing the blog. I think the penny now dropped on what can be done with passing params for a UDF.

Deepak, thanks for keeping me posted.

[#6]

Hi Deepak,

Using variables in expressions is fast -- remember that QlikView uses variables just for text replacement. The contents of the variable, when evaluated as an expression, may require more time or resources just as any expression. The parts of an expression that tend to consume more resources are things that make QlikView work with large amounts of data; things like Sum, Count, Aggr, Max, Min, StdDev, etc.

I'm not aware of any single usage guide to recommend for variables and expressions. The various QlikView bloggers have captured a lot of useful information and there's good information about techniques to use (and avoid) in the forum.

Good luck-

[#7]

Hi Tim,

How do I pass comma seperated value as an argument?.

Thanks,

Anoop

[IAMDV]

Anoop - Have you tried to using ASCII values or encapsulate the string in another variable by using dollar sign expansion?

Good luck!

Cheers,

DV

www.QlikShare.com

[#9]

Hi Deepak,

Can you please provide an example? I have to write a user defined function which should return sum of sales according to the product id that I pass.

Some cases i would like to get more than one product's sum. With one product id function is working fine, if I add two values eg 1, 2 to the function it is not working. I tried with variable but it is not working . Can you please help me on this? ..

Thanks in advance ..

Thanks,

Anoop

[#10]

Hi Deepak,

I tried both the way it is not working..

Thanks,

Anoop