Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
djbloiss
New 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.

Tags (2)
16 Replies

Re: Advanced Expressions as Variables

Can you check these

Variables with Parameters

Qlikview Parameterised Variables

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)
MVP & Luminary
MVP & Luminary

Re: Advanced Expressions as Variables

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
New Contributor III

Re: Advanced Expressions as Variables

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.

MVP & Luminary
MVP & Luminary

Re: Advanced Expressions as Variables

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
New Contributor III

Re: Advanced Expressions as Variables

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.



MVP & Luminary
MVP & Luminary

Re: Advanced Expressions as Variables

Please provide a small example which displayed this behaviour.

- Marcus

djbloiss
New Contributor III

Re: Advanced Expressions as Variables

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
New Contributor III

Re: Advanced Expressions as Variables

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
New Contributor III

Re: Advanced Expressions as Variables

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