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: 
Not applicable

How to insert function as part of dinamic expression created by script ?

Hi guys,

Need some help to solve the following:

I have a table (chart) with a Group as dimension. 

I create expressions dinamically from script.

In one of my expressions, I have to use the function GetCurrentField(GroupName).

The expression works ok as a regular expression in the chart:

Sum(Invoices.LineTotalNIS
Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)  

Where the variable vCurrentField= GetCurrentField(Display2)

In the Script, I define the following expression:

Sum(Invoices.LineTotalNIS
Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)  

If I replace the variable by one of the dimension in the group it works.

I tried also:

Sum(Invoices.LineTotalNIS
Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1, $(=vCurrentField)> Orders.PurchasePriceNIS * Invoices.TQuant)  

And

Sum(Invoices.LineTotalNIS
Sum( Distinct Total <InvoiceKey1, Invoices.KLine, OrdIKey1,
GetCurrentField(Display2)

Orders.PurchasePriceNIS * Invoices.TQuant)  

How can I work it around?

Thanks in advance,

Aldo.

12 Replies
swuehl
MVP
MVP

Hi Aldo,

not sure if I understood what you want to achieve:

I assume you are not wanting to evaluate the expression in your script (i.e. calculating the sums), which I think is not possible (using GetCurrentField() in the script).

So you probably want to set variables to the expression and use the variables in charts expressions?

Could you give some more information about the context of the above lines (e.g. where and how you defined vCurrentField in your script)?

But maybe I just don't get what you are after.

Regards,

Stefan

Not applicable
Author

Hi Stefan,

I am defining the expression in script, but using it in as Chart expression throw variable.

I am defining vCurrentField = GetCurrentField(GroupName) in GUI.

Thanks for taking the time to help,

Aldo.

swuehl
MVP
MVP

Aldo,

do you need to define this variable in the script or would it be possible to define the variable in the GUI?

Not applicable
Author

I can define the variable both in script or GUI

swuehl
MVP
MVP

Aldo,

please have a look at attached - simplified - example. I've put the variable in the GUI, reason is, because I think it's problematic to use the dollar sign expression function call GetCurrentField in the script like that. But if we could use variables set in the GUI, I think a solution to your problem is not far away.

Most of the time I struggle with correct variable evaluation calls.

In the demo, if you drill down into the bar chart, the current field is used by the expression the table chart. I think that's what you want, using a variable, right?

Hope this helps (or at least could serve for further discussions),

Stefan

Not applicable
Author

Stefan,

It is a little more complicated than that.

i am trying to updload an example (qvw) but don't find how to (don't have any icon to upload documents).

Regarding the issue, I need to create the whole expression in the script.

Thanks,

Aldo.

swuehl
MVP
MVP

Please use the advanced editor, there you find an upload option.

Not applicable
Author

I have attached an example.

Please note that (in multibox):

Sales1 is a simple expression and works nice.

Sales2 and Sales3 are some of the ways I am trying to insert the function GetCurrentField(Grp1) into the expression.

Aldo.

swuehl
MVP
MVP

Hi Aldo,

I think the problem is to guard the GetCurrentField() function in the script in a way that it will not be evaluated during load, but then afterwards in the GUI.

(That's why you probably used the variable etc.)

I fought with this some time, but no real answer yet.

What I think could work is putting the expressions as variables in the GUI, I created two new variables as examples. Then evaluate the variables in the chart. I noticed that you want the flexibility to select the variables from a dropdown using a table. So I added a index to the table in the script and used this index to build the variable name to be called dynamically in the tables expression.

So please check out the results for Sales1 and Sales2 in the last column of the table.

This is not exactely what your original approach is like, because I can't hold  the expression variables in the script. But maybe it kind of solve your business problem nevertheless.

Maybe I or someone else find a better solution to guard the functions call in the script, I am not able to spend more time on this approach today (but if you might have questions about the "workaround", don't hesitate to ask).

Regards,

Stefan