Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to get the sells of spain for a month and i have done the following expression, however it is giving me error.
let sell_spain=sum({$<YEAR_MONTH={$'&'(date)},Country={'SPAIN'}>}SELL);
let sell_spain='=sum({$<YEAR_MONTH={$'&'(date)},Country={'SPAIN'}>}SELL)';
i attached you the error that shows on the script.
¿Can you help me please?
Thank you
Hi Pablo,
You can not use the Set Analisys expressions in script. You should use the create of the temp table with grouping COUNTRY and selections COUNTRY = SPAIN and YEAR_MONTH = YOURSEXPRESSION, and then transfer the received value to a variable.
Regards,
Andrey
You cannot use the set analysis in the script convert it to if condition and then sum() should be used with the Group by clause
I think you have 2 things to solve
1)
the $ char, you can use a replace or chr(36)
let exp1 = replace('sum({^ <YEAR={^(=max(YEAR))}>} SELL)', '^', '$');
let exp2 = 'sum({$ <YEAR={' & chr(36) & '(=max(YEAR))}>} SELL)';
2)
the YEAR_MONTH,
you need something like
YEAR_MONTH={ $(=Date(max(date), 'YYYYMM')) }
Also replace the $ as suggested at 1)
I suggest to read, thanks to marcus_sommer
above all this section and define the variables outside Qlikview
I am assuming that you want to create the expression in the load script to use in the front end. As people have already said, you cannot execute set analysis in the script, but you can create the expression. This is what I do:
Set sell_spain = =Sum({$<YEAR_MONTH={#(date)}, Country={'SPAIN'}>} SELL); |
Let sell_spain = Replace(sell_spain, '#', '$');
The set statement does not need quotes, so you can drop the outside quotes. The double = sign means that the variable will be globally evaluated in the front end. Using replace does not cause the expression to evaluated now, and allows me to write the expression in a simpler form than with embedded string computation.
Your second let statement should work though.