Advanced Expressions as Variables

[djbloiss]

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.

[djbloiss]

how come there is no way to attach a qvw?  I can't use advanced editor?  I am trying to add the example.

[djbloiss]

I figured out why I couldn't add a qvw file.  I was replying from my inbox, which doesn't allow you to get in to the advanced editor.  Sorry, as you can see in the file $10 always evaluates as $1(0)

[marcus_sommer]

For me it worked with 10 parameters although I changed a bit the places of the parameters because there wasn't enough fields in your example so that must be a bit creative. Further I believe it will always work if there are no errors within the variable and the expression-call of it:

- Marcus

[djbloiss]

I want to try to replicate that but you don't show how you call the variable exponent.

[djbloiss]

Marcus, Thank you.  I want to try to replicate that but you don't show how you call the variable exponent.

[marcus_sommer]

It's:

$(e_Sls(1, [Invoice Number]=, DateKey=, [Customer Code]=, [Order Date]=, [Transaction Date]=, [Week Count]=, [Ext Sales - Net]=, TOTAL, <DateKey> ))

If you opened the question in a new browser-tab you will see that there is also a qvw attached.

- Marcus

[djbloiss]

sorry I didn't realize you supplied a qvw file.  with that file and more testing I have discovered more.  Here I remove the last 2 arguments and the $10 is evaluated as 1(0), so it only happens if the $10 argument is not passed.  That is the issue.  This variable will be used in certain cases where there is no 10th argument and in those cases it will result in 1(0) which messes up the expression.  I am doing more testing, thank you for your help.   I will reply as I figure out more.