Evaluate() Function

[Jennell_Yorkman]

The Evaluate() function is a string function that takes one text string parameter.  If the string is a valid QlikView or Qlik Sense expression then the evaluated result of the expression will be returned.  If the string is not a valid expression, then Null is returned.  The Evaluate() function can only be used in the script and not in a chart expression.  Evaluate() will do the math for you in the script and return the result.  For example, if you are loading a field whose content is expressions like this:

Then you can view that data in two ways.  You can either view the actual expression or you can use Evaluate() and view the results.

The Evaluate() function is also useful when you are loading a number from a text file or a text field in a database with more than 14 digits.  When that number is loaded into QlikView, it is interpreted as a string since it is too long (more than 14 digits).

Note that while using Evaluate() returns a number, it is only stored with 14 digit precision.  While you may be able to see more than 14 digits, this is just the formatting.  The internal mantissa is only 14 digits.

Evaluate() can similarly be used with variables which can be helpful if you have a value that you would like to use multiple times in the script.

So why use a Evaluate()?  At first it seemed unnecessary to me because the calculation would still be done without the Evaluate() function but then I realized that that is only the case when the values are numeric and not strings.  So Evaluate() is helpful when your expression is in a string format or when the expression is a number with more than 14 digits.  It allows a string expression to be evaluated and the result returned.  It is like a short-cut to solving the expression.  Try it out!

Thanks,

Jennell

[juraj_misina]

Hello Kim,

you are right. I thought this should be easily done, but using Sum() or Avg() did not work. I'm surprised. However - and this is interesting - Concat() and Count() DO work. This enables you to create a workaround for simple cases:

Values:
LOAD * INLINE [
     Value, KPI
     10, Sum(Value)
     20, Sum(Value)
     30, Sum(Value)
     40, Sum(Value)
     50, Avg(Value)
     60, Avg(Value)
     70, Avg(Value)
     80, Avg(Value)
];
Sums:
LOAD
     KPI,
     Evaluate('('&Concat(Value, '+')&')'&If(WildMatch(Only(KPI), 'Avg*'), '/'&Count(Value))) as KPIresult
Resident Values
Group By KPI;

[#3]

Hello Juraj

I was also surprised, I thought I was doing something wrong. But as I understand it now, this is either a bug, or an erroneous description in the reference manual:

evaluate(s )

If the text string s can be evaluated as a valid QlikView expression, the evaluated result of the expression

will be returned. If s is not a valid expression, NULL is returned.

Thanks for your example. I will see if I can implement it in my QV-application, or if I'll settle for a nested-if-statement (an ugly solution in my opinion).

I'm a bit disappointed that the function (evaluate), that looked as it was tailored for the job, didn't work.

Thanks again.

Best regards

Kim

[navaskhan]

You mean if we have 2 column A & B  and third column as A+B ( String ) the Evaluate of A+B will give the Total?

[#5]

Hi there,

Did you ever find a solution to this, really trying to get this right now, but not working out very well.

[beck_bakytbek]

Thanks a lot

very informative, super issue

best regards

beck

[Anonymous]

jmc 100% * 60% this string is also NOT being evaluated. Giving back null. Any solution please?

[eduardo_sommer]

Hi,

This is not a valid expression. It should be (100/100) * (60/100)

Eduardo

[mnu]

Thanks for shining a light on the under-used evaluate function.

I use it to implement data quality rules to focus on data quality issues. Here is a document I've put together on the topic. How to Implement Data Quality Rules.

Matt Nunn

Qlik

[Anonymous]

Is there any way to use evaluate in a macro to get the value of a variable you calculate as an avg(aggr( ?
I have a variable called "AverageOnCountry" that I calculate with an expression and I want to export its evaluation value in Excel using a macro. How can I do that?

[Athira_K]

Hello,
I just wanted to know whether this function evaluate can work dynamically,
will it be working as expected after I apply some filtering ?
Cause Im using a chart with expressions and its making things complicated so can this be used ?