Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
greghlewis
New Contributor

Working with Expressions Defined in Fields

Hello, I'm a self-taught QlikSense developer for the last 2 months, and stuck the last couple of days on what seems to be a simple problem related to how variables are defined --

I'm storing an expression in a field to calculate a KPI, assume field KPI_1_expression holds the expression 'Sum(Sales)'. 

On the front end, I can pass '=$(=KPI_1_expression)' to get the result of the expression.

I'd like to define other variables in Load based on the result of this calculation:
vKPI_1_Percent_To_Target = [result of KPI_1_expression] / KPI_1_target

By defining vKPI_1_result = '=KPI_1_expression', then passing =$(vKPI_1_result) on the front-end, I can surface the result of the calculation with a variable.

I can also pass '=$(vKPI_1_result) + 1', in the front end.

However, in Load, I am struggling to pass that same equation to a variable, and call that variable. For example:
vTest = '=$(vKPI_1_result) +1' -->Front end-->  =$(vTest) <<< ERROR
and
vTest = '=KPI_1_calc +1' --> Front end --> = $(vTest) <<< ERROR
and
vTest = '($(vKPI_1_result)) +1' --> Front end --> =$(vTest) <<< ERROR

The error messages suggest that the '=' sign in my definition of vKPI_1_result is what's problematic. For the past few days, I've tested different ways to wrap vKPI_1_result in Dollar-sign Expressions and parenthesis so I can call it in another variable in Load, to no luck.

Ideally, the final solution should be able to be called with $(x) on the front-end. I imagine it would look something like this:
vKPI_1_Percent_To_Goal ='($( vKPI_1_result)) / KPI_1_target' --> Front End --> =$(vKPI_1_Percent_To_Goal) <<< .98

1 Reply
Partner
Partner

Re: Working with Expressions Defined in Fields

This is an issue based on the execution of the load script trying to expand the dollar sign expansion, when you don't want it to.

Simple solution, load the data with the dollar sign expansion represented by a different character(s) and replace later

Let vKPI = 11;
Let vTest = Replace('?(vKPI)+1','?','$');

kpi.jpg

 

 

Blog: WhereClause   Twitter: @treysmithdev