Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
The following script is in an include statement:
sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
if (([Travel Year]='$(vYearToday)'-1 and [Travel Month]=$(vMaxMonth)),
sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
if (([Travel Year]='$(vYearToday)'-2 and [Travel Month]=$(vMaxMonth)),
sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))));
Also, in same file I have script as below which is working in chart:
Hi,
I think your expression doesn't work because of the nested single quotes that cannot be easily resolved. Generally speaking, you can either use the function REPLACE() to replace single quotes by another symbol and then replace the other symbol back to single quotes.
In your particular case, however, it seems like you should simply remove the first single quote, right after the equals sign, and the rest should be OK. I'm not looking into the validity of the expression logic and into the performance aspects of it (your expression can be simplified and improved substantially).
In broader terms, you could avoid some of these problems if you stored your expressions externally in a text file and loaded them into your load script, generating the corresponding variables in the process of the load. That helps avoiding issues caused by nested quotes, $-sign expansions etc...
I'm describing this technique, along with many other advanced techniques, in my book QlikView Your Business - check it out.
cheers,
Oleg Troyansky
Hey Oleg,
I am using an excel file. When I take the single quotes off, it gives me an error.
Please advise.
Thanks
Could you post your excel file containing the variable definition and the script you are using to read the variables in?
For starters, replace your Excel file with a text file - CSV, for example. Excel files are inconvenient for two reasons:
First, these are not text files, so you can't easily compare two files and find differences using an advanced text editor.
Second, they apply their own quoting and distort your formulas - exactly what's happening to you.
You won't have this problem with a regular text file, not managed in Excel.