Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
djbloiss
Contributor III
Contributor III

Advanced Expressions as Variables

I have been transferring all the expressions that the company uses over to variables that are saved in Excel.  Simple ones like this work no problem: (In Excel)

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9>} [Item sales-net])

and then call it inside Qlikview like this:

$(e_Sls($(vSO_Closed_Only)))

This becomes a problem in 2 areas.

First, there are certain places that we are passing more than 9 arguments in Set Analysis and if I increase the number of arguments with $10, $11, etc. it is interpreted as $1(0), $1(1), etc.  Passing the second digit to the $1 argument. is there a way I can get more than 9 variables passed?

Second, if I am passing a variable that is outside the set analysis, it won't work. I tried the following: (In Excel)

sum({<$1, $2, $3, $4, $5, $6, $7, $8>} $9 [Item sales-net])

and then call it inside Qlikview like this:

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report]) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],,,) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],Null,Null,Null) TOTAL <[Invoice Number]>)

and

$(e_Sls_Test(SO_Closed={1}, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, [Source for PG Report],,,,TOTAL <[Invoice Number]>))

None of them are working.  It is not recognizing the TOTAL <[Invoice Number]> in any of them.

16 Replies
Anil_Babu_Samineni

Can you check these

Variables with Parameters

Qlikview Parameterised Variables

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

AFAIK there is no limitation to the number of parameters and I checked it with 12 parameters and it worked like expected - even for each other part of the expression like the TOTAL.

The reason why it didn't work by you is that there are any failures within your expression-variable-construct - one is here: [Customer Terms Code]-={60,90} because there is a comma included and so QV interpret it as two parameters of: [Customer Terms Code]-={60 and 90} which of course leads to an error. There is no way to mask the comma in any way - each comma will be always treated as parameter-delimiter.

In general there are workarounds possible by using a different delimiter for it the inside-comma and replacing it within the variable and/or by using chr(44) within a string-concat - but it won't be always sensible because of the needed complexity.

Saying this I'm not sure that your approach to replace all expressions with variables is a wise decision. Of course it's possible but it adds a huge amount of complexity and additionally efforts to develop and maintain the applications. In my opinion it's an overuse of the idea why variables does exists and for what they are useful.

Nevertheless the above mentioned comma-problem could be bypassed with the following writing:

[Customer Terms Code]-={(60|90)}

- Marcus

djbloiss
Contributor III
Contributor III
Author

Marcus, what syntax did you use for the 12 parameters? because as I mentioned $10 was interpreted as $1(0) where it viewed that as argument 1 passing 0 to it.  And how did you get the Total portion to work?  It is not showing up.  I am not currently having an issue with the comma.  If after I get the formula working, and that is a problem then that would be a good workaround but I am not up to that point yet.

When I look at the label for the expression, I can see how it is interpreted and it is not being interpreted correctly.

marcus_sommer

I'm just added 11 fields like Year =, Month = to the set analysis and the 12 parameter was TOTAL <Field>. Where did you see that $10 is handled as $1(0) ? Did you look at the expression-interpretation within an empty expression-header of a table-chart?

Further like above mentioned the following [Customer Terms Code]-={60,90} is treated as two parameters, because of the comma in it.

- Marcus

djbloiss
Contributor III
Contributor III
Author

that isn't what I am asking.  You are doing a variable directly in Qlikview.

I am creating this as a variable:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9>} [Item sales-net])

or this:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9, $10>} [Item sales-net]

Then I am calling the variable like this.

$(e_Sls(Year=, Month=, Day=, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, SO_Closed={1}, SlsPerson=, Special_Offer={2}, Week={52})


that is evaluated like this:


sum({<Year=, Month=, Day=, [Customer Terms Code]-={60,90}, [Chain store code]={'PAN'}, Item=, SO_Closed={1}, SlsPerson=, Special_Offer={2}, Year={52}>})


the last argument $10 believes that I am calling $1 with 0 as an argument for it. it ignores the argument that I pass in the 10th place.

"Did you look at the expression-interpretation within an empty expression-header of a table-chart" Yes, that is how I know it is evaluating like that.



marcus_sommer

Please provide a small example which displayed this behaviour.

- Marcus

djbloiss
Contributor III
Contributor III
Author

I will put something together.  However, I have figured out more in the process.  The Total variable is not an issue.  Instead the issue you pointed to of the comma being an issue came in to play; what it did was split the comma separated values in to two separate arguments making the total amount of arguments more than I declared in the expression variable. So now I am working on that and the issue of passing more than 9 arguments to the variable.

djbloiss
Contributor III
Contributor III
Author

Here is my Expression as a variable:

sum({<$1, $2, $3, $4, $5, $6, $7, $8, $9, $10>} [Item sales-net]

Here is what my expression in Qlikview:

if(ItemForPanGregorian AND [Chain store code]='PAN', $(e_Sls_Test(sum, SO_Closed={1}, [Source for PG Report]=, [Customer Terms Code]-={60|90}, [Chain store code]={'PAN'}, Item=, '', '', TOTAL <[Invoice Number]>)))

and the picture shows what qlikview evaluates it as.

Issue.jpg

as you can see the $10 is being evaluated as sum(0), not, as in this case blank.

djbloiss
Contributor III
Contributor III
Author

and here is a very basic qvw that shows $10 always evaluates as 1(0)