Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my load script I define a variable with the following expression.
Set vCurrentUserId = Upper(SubField(OSUser(), '=', -1));
I can then display user in Text object like so:
$(vCurrentUserId)
And it works fine.
But I want to use this in my other variables that are defining set expressions.
1. This works when defined in chart expression and in load script, of course
//chart
sum({ $< BillingYear={'2020'} UserID={'FRED'}> } Units);
//load
Set mySum = sum({ $< BillingYear={'2020'} UserID={'FRED'}> } Units);
2. These work only when defined in chart expression but not in load script
//chart: working
sum({ $< BillingYear = {'2020'}, UserID={'$(=Upper(SubField(OSUser(), '=', -1)))'}> } Units);
//load: not working
Set mySum = sum({ $< BillingYear = {'2020'}, UserID={'$(=Upper(SubField(OSUser(), '=', -1)))'}> } Units);
//chart: working
sum({ $< BillingYear={'2020'} UserID={"$(=Upper(SubField(OSUser(), '=', -1)))"}> } Units);
//load: not working
Set mySum = sum({ $< BillingYear={'2020'} UserID={"$(=Upper(SubField(OSUser(), '=', -1)))"}> } Units);
3. These attempts don't work neither in chart expressions nor in load script.
Set mySum = sum({ $< BillingYear={'2020'} UserID={$(vCurrentUser)}> } Units); // Error
Set mySum = sum({ $< BillingYear={'2020'} UserID={"$(vCurrentUser)"}> } Units); // Returns 0
I guess there must be a way to A) Use the OSUser() inside the expressions, and B) predefine a variable that extracts the required part of the string from the OSUser().
I believe your problem is because of the use of "$()" in your SET value. Script interpretation will always perform Dollar Sign Expansion -- substitution -- when encountered. So the "Upper(SubField(OSUser(), '=', -1)" expression is getting evaluated during the script run, instead of in the chart. If you look at your variable in the variable editor you'll see that the $() has already been replaced.
There are a number of ways to work around the $( problem. But first, if you are ok with coding the OsUser() function in the variable expression, you don't need the $ at all. You can write it:
SET mySum = sum({ $< BillingYear = {'2020'}, UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}> } Units);
If you need to use $(, like for example you want to reuse vCurrentUser, you have several choices. I'm going to use some code close to yours because I have it laying around. You can adapt to your expression.
First the problem.
SET vCurrentUser = =SubField(OSUser(), '\', -1);
SET e.Sales = Sum({<User={"$(=$(vCurrentUser))"}>}Sales);
e.Sales won't work because the $(= will get evaluated during the script run. If I look at the variable value in the Variable Editor I see:
Sum({<User={""}>}Sales)
Alternative 1 -- Use LET to break the "$" and "(" apart and concatenate back together.
LET e.Sales = 'Sum({<User={"$' & '(=$(vCurrentUser))"}>}Sales)';
Alternative 2 -- Use @ as a placeholder for $ and fixup with Replace().
LET e.Sales = replace('Sum({<User={"@(=@(vCurrentUser))"}>}Sales)', '@', '$');
Other alternatives include using MapSubstring() instead of Replace(), and loading the Variables from an external file.
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Lo and behold, this actually works. I'd never thought this would work:
UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}
Does this mean that when {"= is used, what follows replaces what is before ={, but only in load script, and in chart script it is slightly different?
As for the reusability, I ended up doing this:
Set vUIDExpression = UserID={"=UserID=Upper(SubField(OSUser(), '=', -1))"};
Set setLastMonth = BillingMonth = {$(PreviousMonth)}, BillingYear = {$(CurrentYear)}, $(vUIDExpression);
Set expLastMonth = sum({ $< $(setLastMonth ) > } Units);
Set expLastMonthSales = sum({ $< $(setLastMonth ) > } Sales);
// etc...
All of it works now by being declared only load script, except the final expLastMonthSales is being used in chart expressions and visualizations.
Hi @alenb
As I understand it, you would like to filter the data according to the OS user. Why don't you use section access?
I already have a bunch of similar expressions that now need to have also the user condition added.
As for Section Access, maybe I could pre-filter the data there, but I'm not sure how to do that either.
But is it not possible to filter data "on the fly" in the expressions like I posted? It seems that in some cases it does work and in others not.
I believe your problem is because of the use of "$()" in your SET value. Script interpretation will always perform Dollar Sign Expansion -- substitution -- when encountered. So the "Upper(SubField(OSUser(), '=', -1)" expression is getting evaluated during the script run, instead of in the chart. If you look at your variable in the variable editor you'll see that the $() has already been replaced.
There are a number of ways to work around the $( problem. But first, if you are ok with coding the OsUser() function in the variable expression, you don't need the $ at all. You can write it:
SET mySum = sum({ $< BillingYear = {'2020'}, UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}> } Units);
If you need to use $(, like for example you want to reuse vCurrentUser, you have several choices. I'm going to use some code close to yours because I have it laying around. You can adapt to your expression.
First the problem.
SET vCurrentUser = =SubField(OSUser(), '\', -1);
SET e.Sales = Sum({<User={"$(=$(vCurrentUser))"}>}Sales);
e.Sales won't work because the $(= will get evaluated during the script run. If I look at the variable value in the Variable Editor I see:
Sum({<User={""}>}Sales)
Alternative 1 -- Use LET to break the "$" and "(" apart and concatenate back together.
LET e.Sales = 'Sum({<User={"$' & '(=$(vCurrentUser))"}>}Sales)';
Alternative 2 -- Use @ as a placeholder for $ and fixup with Replace().
LET e.Sales = replace('Sum({<User={"@(=@(vCurrentUser))"}>}Sales)', '@', '$');
Other alternatives include using MapSubstring() instead of Replace(), and loading the Variables from an external file.
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Lo and behold, this actually works. I'd never thought this would work:
UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}
Does this mean that when {"= is used, what follows replaces what is before ={, but only in load script, and in chart script it is slightly different?
As for the reusability, I ended up doing this:
Set vUIDExpression = UserID={"=UserID=Upper(SubField(OSUser(), '=', -1))"};
Set setLastMonth = BillingMonth = {$(PreviousMonth)}, BillingYear = {$(CurrentYear)}, $(vUIDExpression);
Set expLastMonth = sum({ $< $(setLastMonth ) > } Units);
Set expLastMonthSales = sum({ $< $(setLastMonth ) > } Sales);
// etc...
All of it works now by being declared only load script, except the final expLastMonthSales is being used in chart expressions and visualizations.
UserID={"=UserID = Upper(SubField(OSUser(), '=', -1))"}
None of this is evaluated in the script. It is only stored in the variable for later use by the chart. This is the "Advanced Search" syntax. The "=" after " indicates that instead of literal text, what follows is an expression that needs to be evaluated to or false. So the diagram looks roughly like this:
UserID = { True | False}
Rows will included where the expression is True.
-Rob