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

[#2]

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?

[IAMDV]

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,

DV

[#4]

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?

[IAMDV]

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

[IAMDV]

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

Cheers - DV

[IAMDV]

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

[#8]

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

[IAMDV]

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

[#10 · Accepted solution]

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'))