topic Expressions in Variables in QlikView

Expressions in Variables

Anonymous — Thu, 10 Dec 2009 06:07:10 GMT

I am very new to Qlikview and have a question about using variables in chart expressions. I have a formula that I would like to store in a variable. The reason for wanting to store in a variable is that the formula is going to be used in MANY charts within my application, and I would like to maintain the formula in one spot....in case it needs to change. Here's the formula:

Sum(REG_HOURS_NUM + OVERTIME_HOURS_NUM)

I'm placing the formula in a variable called vTest. I then create an expression in my chart as $(vTest). The formula calculates a number, but seems to be unaware of dimensionality in the chart. I have a dimension in my chart for the day of the month, I get the same number, which appears to be just a grand total, for each record in my straight table.

Any help/suggestions would be greatly appreciated.

Thanks

Expressions in Variables

rwunderlich — Thu, 10 Dec 2009 06:49:10 GMT

Leave the "=" out of the variable definition. The "=" causes it to be evaluated once, when what you want is the expression text.

Use the "=" in the chart expression instead (if required).

=$(vTest)

-Rob

Expressions in Variables

Anonymous — Thu, 10 Dec 2009 08:05:51 GMT

Rob,

Thanks for the quick response. It looks like that did it!

I have another expression that uses the Aggr function......shouldn't have any problems with doing that one the same way.....right?

Expressions in Variables

rwunderlich — Thu, 10 Dec 2009 11:05:50 GMT


DFreeman wrote:shouldn't have any problems with doing that one the same way.....right?

Correct. Always Leave the "=" out the variable definition if you want the expression evaluated in the chart.

Please remember to mark the correct answer with "Verify Answer" button.

-Rob

Expressions in Variables

Anonymous — Mon, 14 Dec 2009 22:02:09 GMT

Sorry Rob.....didn't know I had to do that. This was my first post on this site. I have now "Verified". Thanks again for the help.

I was thinking that I would load these variables in my load script.....that way I would have a good spot to maintain them. When I try to assign these variables in the load script, I receive an error......specifically in those formulas with the Aggr function. Am I trying to do something I'm not allowed to do?

Expressions in Variables

rwunderlich — Mon, 14 Dec 2009 22:22:59 GMT

You should be able to reference the variable in an expression with this syntax:

$(var)

If that doesn't solve your problem, please post your expression.

-Rob

Expressions in Variables

Anonymous — Mon, 14 Dec 2009 22:39:54 GMT

I'm not sure my last email was very clear. I'm actually having the problem trying to declare the variable.....not when trying to use it.I have this statement in my load script:

Let fPatDays=sum(Aggr(CENSUS_NUM,CALENDAR_DAY,Hospital));

Here is the error message I get when I try to load my app:

Error in expression: AGGR is not a valid function.

Any advice would be greatly appreciated.

Expressions in Variables

rwunderlich — Tue, 15 Dec 2009 06:23:31 GMT

"LET" evaluates what is to the right of the = sign. In this case, you want to use SET instead, which does not evaluate the value.

SET fPatDays=sum(Aggr(CENSUS_NUM,CALENDAR_DAY,Hospital));

If you wanted to use LET (unnecessary in your example), enclose the value in single quotes.

Let ' fPatDays=sum(Aggr(CENSUS_NUM,CALENDAR_DAY,Hospital)) ';

Mixing literals and expressions would also use LET:

LET vFilemsg = 'The time of the source file is ' & filetime('mydata.txt');

-Rob

Expressions in Variables

Anonymous — Tue, 15 Dec 2009 09:54:17 GMT

That worked great.....thanks again Rob. We haven't implemented Qlikview yet.....probably going to happen sometime in Feb 2010. We've already bought the software and are developing just in the client......waiting for the server install.

You have been a really big help to me. I really appreciate it.

I'm still trying to figure out how this site works. I noticed earlier that you asked me to "Verify Answer" on your solution.....which I have done again on this latest solution. I'm guessing this is how you have your Top 10 ranking. The reason I'm asking is that I have another issue I'm trying to deal with that is totally unrelated to this one.....and you seem to have all the answers 🙂 and I also don't want to monopolize too much of your time. I'm trying to decide if I should just include it in this thread or start another one. Your thoughts?

Thanks again.....I hope I can return the favor one day.

Expressions in Variables

rwunderlich — Tue, 15 Dec 2009 13:07:09 GMT

I don't know if the "Verify Answer" gives points. I don't understand the points system. I note that mine just dropped by a few thousand. Jason, our Webmaster, is known to be arbitrary and capricious. 😉

My primary reason for encouraging Verifies is it helps guide members in identifying threads that still need attention.

I recommend that you start a new thread if you have a new question or issue. It's likely to get more visibility and responses that way.

If you are new to QV you may want to get the QV Cookbook from:

http://robwunderlich.com/Download.html

It has some useful examples of basic tasks.

Welcome to Qlikview and the Community!

-Rob

Expressions in Variables

Anonymous — Tue, 15 Dec 2009 21:35:34 GMT

Thanks again Rob.....and thanks for the cookbook!