This isn't a question. I tried to add this to the blog because it would be useful to the community but it wouldn't let me.
I am new to Qlikview and my company gave me a task to convert all our heavily used expressions in to variables. We want to do this because:
1) Improved maintainability -- you don't have to edit all occurrences of an expression in an application, but only once
2) Faster development - you won't have to type as much
3) Better readability -- you have much better readability of your expressions because their (sometimes complex) logic is actually replaced with its brief explanation
As I researched this topic and tested on my qlikview documents, it came apparent different people have posted pieces necessary for this to work but not all of it and those pieces have been scattered around in many different documents and posts. This took me a couple of days to compile, so I decided to spread the knowledge for others to benefit.
I found these sources that aided me on my task:
http://www.johndaniel.com/index.php/when-values-arent-values-null-handling-in-qlik/
QlikView #022: Storing Expressions into Variables - YouTube
Comma problem (,) workaround for dollar sign expansion with parameters
Now, what is the issue that none of them fully detailed?
Lets start with a simple expression:
Sum([Item sales-net])
And you use it like follows:
| Variable Name | Variable | Call in Chart Object |
|---|
| e_sls | Sum([Item sales-net]) | $(e_sls) |
but what if you then want to do an Average of [Item sales-net]. Do you need to add a separate variable? No, a simple change allows you to use any function to start the expression:
| Variable Name | Variable | Call in Chart Object |
|---|
| e_sls | $1([Item sales-net]) | $(e_sls(Sum)) |
Coming along fine but now what if your set analysis uses multiple parameters? Well you can do this:
$1({<$2, $3, $4>} [Item sales-net])
but that has the problems of forcing you to specify how many parameters you want to use in your set analysis, so it really isn't expandable at all. It also comes to a problem with if you want to use any type of parameter after the set analysis but that will be detailed later. So how do you solve this problem?
| Variable Name | Variable | Call in Chart Object |
|---|
| e_sls | $1({<(=Replace('$2', ';', ',')>} [Item sales-net]) | $(e_sls(sum, Item=; slsperson=) |
What I needed to do was use semicolons in place of commas for all parameters that would be in set analysis and replace it with commas. With this most current version every comma changes the variable and every semicolon creates another parameter within the set analysis. However, this still has trouble. if I don't use set analysis at all, I cannot use it this way and would again need to create a new expression. So how do we fix this:
| Variable Name | Variable |
|---|
| e_sls | $1($(=If(Len('$2')=2,'', '{<' & REPLACE('$2', ';', ',') & '>}')) [Item sales-net]) |
What I needed to do is test if $2 is passing a value. Now you can call it the same way, if you are using set analysis, but if you don't it will still work correctly. This is starting to get pretty powerful now but what if I then want to use a total qualifier in some cases? Easy just add another variable but this has to work also in case you don't want to use the Total qualifier
| Variable Name | e_sls |
|---|
| Variable | $1($(=If(Len('$2')=2,'', '{<' & REPLACE('$2', ';', ',') & '>}')) $(=if(Len('$3')=2,'','$3')) [Item sales-net]) |
| Call in Chart Object | $(e_sls(sum, Item=; slsperson=, Total<[Invoice Number]>) |
I have to test for the length in this case because without it, it would put $3 in the expression and it would not work. In this case, it is very functional and expandable. I would like to point out a few more things. If you see above when the variable is called in the chart object when you use the Total qualifier you need to use a comma again. Next if you want to use a variable inside this variable, do it like this with dollar sign expansion:
$(e_sls(sum, $(vSO_Closed_Only); slsperson=, Total<[Invoice Number]>)
Finally, if the variable you want to pass in has multiple strings delimited by a comma like this as an example:
[$(=vCalendar&' Year')]=, [$(=vCalendar&' Month')]=, [Transaction Date]=, [$(=vCalendar&' Week Ending Date')]=, DateKey={$1}
Make sure to change the commas to semicolons. If not it will try to pass it through in to another variable $2, $3, etc. So it should look like this:
[$(=vCalendar&' Year')]=; [$(=vCalendar&' Month')]=; [Transaction Date]=; [$(=vCalendar&' Week Ending Date')]=; DateKey={$1}
