topic Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate() in QlikView

AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Wed, 23 Dec 2015 14:17:50 GMT

Hi!

ActiveDocument.Evaluate() works fine at MSGBOX, for example:

msgbox ActiveDocument.Evaluate("subfield('a,b,c', ',', 2)")

But dnt work at interface TextObject with AllowMacroFunctionsInExpressions=1

Interface object must contain "=fEvaluate('subfield('a,b,c', ',', 2)')" without double quotes.

Work example (use Eval() VBS function):

FUNCTION fEvaluate(N)

fEvaluate = Eval(N)

END FUNCTION

Dnt work sample:

FUNCTION fEvaluate(N)

fEvaluate = ActiveDocument.Evaluate(N)

END FUNCTION

Can someone help me make it work?

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

petter — Wed, 23 Dec 2015 14:54:55 GMT

A first note:

the Eval() function of VBS is something completely different from the Evaluate member of the QlikView Automation API.

Why do you want to evaluate something that you can do directly in a textbox expression anyway? Is this just a "toy" example that you pose - not what you really need to do with Evaluate?

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Wed, 23 Dec 2015 15:31:20 GMT

Yes its a toy example.

My case need to calculate text string expression contained in a field linked to title field.

For real example i need to build chart of KPIs, with dimention and expressions like this picture:

Now i beat it using VALUELIST('BUDGET','ACTUAL','VARIANCE') as dimention and expressions like

IF(

VALUELIST('BUDGET','ACTUAL','VARIANCE')='BUDGET'

,IF(VALUELIST('BUDGET','ACTUAL','VARIANCE')='ACTUAL'

,IF(VALUELIST('BUDGET','ACTUAL','VARIANCE')='VARIANCE'

)))

But its simple case and i need to add more dimentions and expressions.

When i put first column as dimention and convert expression as other columns, ill happy. 😃

Using Evaluate() this case must work with script:

LOAD * INLINE [

Title, Budget, Actial, Variance

Total Flights, sum(A), sum(B), sum(A)-sum(B)

]

And chart with dimention Title and expressions like =fEvaluate([Budget]) for Budget column.

--kind refards.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Wed, 23 Dec 2015 15:35:10 GMT

What should be the use of macro-functions in your case?

- Marcus

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Wed, 23 Dec 2015 15:40:28 GMT

previous post corrected

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Wed, 23 Dec 2015 16:07:11 GMT

You could use The Magic of Dollar Expansions for this, like: $(=Budget)

- Marcus

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Wed, 23 Dec 2015 16:42:40 GMT

Marcus, $() calculate once per chart, am tryin' it already. May be other solution?

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Wed, 23 Dec 2015 19:13:03 GMT

What didn't work? I think it would be helpful you post more details. In general it's possible to create a table-chart without a real dimension and assign certain expression to the virtual dimension without macro-functions (are the absolute worst case) and $-sign expansion (which could be very often applied).

- Marcus

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

petter — Wed, 23 Dec 2015 20:56:17 GMT

Could something like this work for you?

I have attached the QVW too for you to have a look at...

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Wed, 23 Dec 2015 23:47:37 GMT

petter-s thanks, very interesing example.

But it has same logics like valuelist() case.

The target of my question is understand of "why Evaluation() function which must return text, cant"?

And globally this solution can extend current functionality of reportiong.

Marcus, now, using of $() is closest solution, but it cant get array of arguments linked to dimention, it calculates once per chart and incert single (not dimention dependent) string in $(=) or numeric in $(#=) value.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

petter — Thu, 24 Dec 2015 00:50:02 GMT

When you call a VBScript function in a Macro this function can't use the QlikView object model. So you are limited to using standard VBScript.

That is why there is a difference between calling the same VBScript function from a Macro and from an expression in the QlikView UI.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Thu, 24 Dec 2015 08:13:58 GMT

Really? Are there ways to use the object model of the current document in the interface? In my opinion, a bit strange limitation.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

petter — Thu, 24 Dec 2015 08:20:28 GMT

I did some testing to verify it. It is really not so strange because making a sort of loop-back call like you want to do might lead to serious performance issues and other issues. One of the environments should be the host and the other the client. What you wish for is that both are hosts and clients to each other at the same time...

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Thu, 24 Dec 2015 08:31:20 GMT

Maybe. But at the moment into QlikView is not possible to interpret a text string as an expression. Rather at the moment only through the Eval() of VBS.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Thu, 24 Dec 2015 11:08:14 GMT

Macro functions within the gui-objects are really rarely useful - only by iterations or other kind of loops it's easier or the only possibility - everything else could be done with the normal qlikview functions. Using variables as user-defined functions, parametrized and with $-sign expansion are very powerful and flexible: Variables

- Marcus

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Mon, 28 Dec 2015 10:31:38 GMT

I maked sample to try variables, but any arguments need to be sended in $( ).

But $( ) dont see chart dimentions.

Can you help me here?

Included solution with vbs eval() function, but it beat only simple expressions.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Mon, 28 Dec 2015 13:25:02 GMT

I don't understand yet what do you are trying to do. QlikView isn't excel where each cell could have their own calculation completely independent to other cells or data - qv processed data which are structured in fields and tables.

There are exceptions possible like in the example from Petter which is quite near to your example and I didn't understand what isn't suitable (at least as starting point). I think your approach with macro-functions and any eval-functions led into a deadlock.

- Marcus

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

Mon, 28 Dec 2015 14:10:57 GMT

Am tryin to avoid charts with 300+ expressions or expressions with 5k symbols of code, when making reports like this.

* each row of included sample has different calculations. Now i beat it using mapping and many IFs.

** and included pdf isnt hardest sample 😃

When you answered my question about macroses and evaluate function, you said i can make it using variables and arguments. I just ask how to use variables to give em field value as parameter and not isolate it from chart dimention.

Peter's sample require expression, of many thouthands symbols inside. Its very difficult to implement and support.

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

petter — Mon, 28 Dec 2015 16:56:52 GMT

With all due respect I think you are over-complicating this quite a bit. You don't need 300+ expressions with 5k symbols of code.... If you you really do then you don't need a BI software tool you need to program in some programming language.

What you attached looks very similar to things that people solve everyday with QlikView and Qlik Sense without having to resort to endless numbers of expressions. Often when people approach new tools they will over-complicate things - that is very common - even with Qlik products.

You can't start with what you think is the solution - you have to come up with a clearly stated requirement without demanding that it should be solved in a particular way. Then most likely someone can advise you on how to approach it in an efficient way in QlikView.

It might very well be that you should pre-calculate a lot of the data while you run the load script. Then again it might be that you shouldn't. It all depends on speed, size of the data model, the necessary flexibility and freedom you need in your UI while doing your analysis. It is all a matter of balance and sometimes a fine art and not a simple checklist you can use without modification to any kind of problem.

There are plenty of people here on the forum that will be more than happy to help you - just be clear about requirements and what kind of data that you have at your disposal initially. Providing some test data and some figures about the nature of this data will speed the process tremendously.

best regards

Petter Skjolden

Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

marcus_sommer — Tue, 29 Dec 2015 10:19:40 GMT

I agree completely with Petter. Thinking in the structures of other languages/tools like SQL or excel is rather obstructive then helpful.

Nevertheless I have looked for a way to automate such expressions and with the use of a structured expressions-concat with rowno() and a parametrized expression-variable within a pick-expression (a larger pick could be created within a loop) comes it quite near to your idea (a direct evalute won't be possible neither with $-sign expansion or macro-functions).

But you should really rethink your approach and use the power of qlikview which is the associative datamodel and where the main-logic is within the dimensions and quite simple expressions like sum(sales/budget) or count(order) against those dimensions is enough.

- Marcus