topic Re: User Defined Function (UDF) using Variables in QlikView

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 09:58:17 GMT

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

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 10:10:29 GMT

Hi Deepak,

If This Year & Previous Year are expressions defined in your object, why don't you just use the labels to calculate the dim on the third expression. You could also use inter-record functions (top()/above() or first()/previous() depending on if inter-column or inter-row, respectively) or am I just talking nonsense.

Can you give more info on the structure of your object, expressions and DB table if necessary?

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 10:20:55 GMT

Hi Cheenu,

Happy to see your reply on my post. I am sorry, I don't have understanding about inter-record functions. Probably I need to be more clear on my request. Let me try to rephrase the question...

I have Speedometer (Guage Chart) showing Current Year Score vs Previous Year Score. I am using needle to show the current year score and using the Segment 2 to show the previous year score in different color. And now I wanted to show Percentage difference these 2 values. I could directly use the variables to calculate the percentage difference. However, I need to calculate the same for multiple charts, so I thought it would be easy to use UDF.

I am also attaching the image of the chart with fictitious data 🙂

I hope this makes sense...

Cheers,

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 11:12:37 GMT

Hi Deepak,

If you want to re-use the % diff, is there something preventing you from defining This Year and Last Year in 2 separate vars and then creating a 3rd var based on these 2 vars with number formatting, i.e.

var1 = sum({$<Year={$(=max(Year))}>} Sales), front-end you call for =$(=var1).

var2 = sum({$<Year={$(=max(Year)-1)}>} Sales), front-end you call for =$(=var2).

var3 = num(($(=var1)-$(=var2))/$(=var2), '##.## %') , front-end you call for =$(=var3).

Does this work?

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 11:22:02 GMT

Cheenu - Thats exactly the same I am doing now. However the calculation behind the var1 is almost 3 lines long based on few conditions and same is the case with Var2.And I have these Var1 and Var2 changing for each product / Sales person. So it is becoming labourous process while calculating the percentage difference.

I can still use these expressions to get the percentage difference but it is not readable at all. I thought it would be simpler to define an UDF which accepts Var1 & Var2 and giving the output as Percentage difference. Assuming that If I can use the UDF my expression would be simple...

=fPercentageDiff (Var1, Var2)

instead of 5-6 lines long and very difficult to read.

I'll look forward to hear your suggestions.

Cheers - DV

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 14:17:05 GMT

Cheenu / Others - Any help on this one is much appreciated. Thanks in advance.

Cheers - DV

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 15:38:11 GMT

Miguel/ Ashfaq/ Rocky/ Iassen/ Sunil/ SriVidhya/ John - Please help on this one...

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 16:10:58 GMT

Does this help?: http://qlikviewmaven.blogspot.com/2011/04/variable-that-acts-like-user-defined.html

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 16:28:19 GMT

Hi Tim,

This is brilliant technique. Many thanks to you. Logically I understood how this works. However, I have 2 variables to pass as arguments. So how does it work with 2 variables? Sorry I can't get my head around this one.

Thanks again for your time.

Cheers - DV

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 16:55:10 GMT

If you want to use two arguments just add $2 to your variable value and it will be replaced by the second argument when you execute it. These are "positional" arguments - the first one replaces $1, the second argument replaces $2, third argument replaces $3, etc.

The arguments are separated by commas similar to other functions you might use. So, using the example from the blog posting, you might code something like this in your chart expression:

$(telno(CUST_PHONE,'ext 55'))

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 17:14:35 GMT

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

User Defined Function (UDF) using Variables

IAMDV — Mon, 04 Apr 2011 17:36:47 GMT

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

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 17:52:26 GMT

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)

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 17:54:23 GMT

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.

User Defined Function (UDF) using Variables

Mon, 04 Apr 2011 17:56:28 GMT

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-

Re: User Defined Function (UDF) using Variables

Fri, 14 Jun 2013 09:58:40 GMT

Hi Tim,

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

Thanks,

Anoop

Re: User Defined Function (UDF) using Variables

IAMDV — Fri, 14 Jun 2013 10:20:47 GMT

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

Good luck!

Cheers,

www.QlikShare.com

Re: User Defined Function (UDF) using Variables

Fri, 14 Jun 2013 10:29:02 GMT

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

Re: User Defined Function (UDF) using Variables

Fri, 14 Jun 2013 10:54:02 GMT

Hi Deepak,

I tried both the way it is not working..

Thanks,

Anoop