AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

[#1]

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?

[petter]

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.

[#3]

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

[petter]

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...

[#5]

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.

[marcus_sommer]

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

[#7]

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.

[marcus_sommer]

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

[#9]

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.

[petter]

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

[marcus_sommer]

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