topic Reuse Complex Expression in Multiple Objects with Different Parameters in QlikView

Reuse Complex Expression in Multiple Objects with Different Parameters

Fri, 06 Aug 2010 22:51:53 GMT

Hi everyone, I have a complex expression that I'm using throughout my QlikView app in almost all of my objects. The expression changes slightly between each object. I tried putting the expression into a variable, however how do I pass a parameter to a variable? In essence I'm almost creating a function call.

For example, I have a Profit & Loss statement which has 5 expressions. The first expression calculates the value for the current period, next expression is previous period, expression after that is the different, expression after that is the previous year same period, etc, etc.

The expression looks like this:

Current Period:

num((

if(Measure='Device ASP - CDMA' OR Measure='Device ASP - C2K' OR Measure = 'Device ASP - WCDMA' OR Measure = 'Shares Outstanding',

avg (if(SubmissionQuarter=VersionQtr,Data)),

if(Measure = 'Gross Profit %',

sum(if(AllMeasures='Gross Profit' AND SubmissionQuarter=VersionQtr,Data))/sum(if(AllMeasures='Total Revenue' AND SubmissionQuarter=VersionQtr,Data)),

if(Measure = 'Operating Expenses %',

sum(if(AllMeasures='Operating Expenses' AND SubmissionQuarter=VersionQtr,Data))/sum(if(AllMeasures='Total Revenue' AND SubmissionQuarter=VersionQtr,Data)),

.....

it continues

For the next period, all that changes is the statement of SubmissionQuarter = PrevVersionQtr instead of VersionQtr.

Is there a way to create a function and then pass it a parameter or use variables to achieve this somehow?

Thanks,
Steve

Reuse Complex Expression in Multiple Objects with Different Parameters

johnw — Fri, 06 Aug 2010 23:41:34 GMT

Attached is an example of creating fake functions using variables and with two parameters each.

Edit: To give a more direct answer, you put a parameter in a variable with $1, $2, etc. So you'd replace VersionQtr with $1 in your variable. Then you should be able to use $(MyFunction(VersionQtr)), $(MyFunction(PrevVersionQtr)), etc.

Reuse Complex Expression in Multiple Objects with Different Parameters

Fri, 06 Aug 2010 23:49:11 GMT

Excellent!! Thanks John, exactly what I was looking for. Odd though that the variable editor doesn't like the syntax when you enter $1, it says error, but the function does indeed work. Probably a bug in QlikView. I'm surprised by this feature, wish it was documented somewhere.

Thanks again!!

Reuse Complex Expression in Multiple Objects with Different Parameters

johnw — Sat, 07 Aug 2010 00:02:13 GMT

Well, searching on $1 in the reference manual, I found "Dollar-Sign Expansion with Parameters". It's pretty brief, but at least it's written down somewhere. It IS surprising that QlikView is giving you an error. I was briefly worried that the error meant that even if it still worked, it wasn't officially supported any more. But I figure as long as it's in the 9.0 reference manual, we're safe.