topic Re: Execute different expressions on a chart using macro in QlikView

Execute different expressions on a chart using macro

Wed, 25 Nov 2020 16:16:04 GMT

Been playing with this for a while as a workaround to the Pick function which doesn't scale at all with large volumes of data. This QVW works fine if I change the script to have $(v1) rather than v1. But works only for the non-aggregate functions. e.g. 2*2, 3*3 etc.

The moment I change to Sum(), it doesn't work.

All I want is a simple model which will have something like this.

Metric Exp1 Exp2

M1 m1firstexp m1secondexp

M2 m2firstexp m2secondexp

and the variables are defined like this.

m1firstexp -> sum(value1)

m1secondexp -> sum(value2)

m2firstexp -> sum(value3)

m2secondexp -> sum(value4)

As in the attached file, I want to use Metric as the dimension and dynamically evaluate the respective first and second expressions. Anyone has any ideas kindly let me know that will be of great help.

P.S: Kindly avoid suggesting Pick. I have had enough of it. Sorry.

Re: Execute different expressions on a chart using macro

Gysbert_Wassenaar — Mon, 02 May 2016 16:09:37 GMT

I'm afraid your document doesn't make much sense to me. I don't quite understand what you're doing. But macro's are a bad idea. Macro's are generally very slow compared to the qlikview calculation engine. Using macro's can be great way to cause performance problems.

If you want to be able to pick between expressions in a chart then there are better ways. First of all have a look at these blog posts:

How Not to Choose an Expression | Qlikview Cookbook‌

How to Choose an Expression | Qlikview Cookbook

And now have a look at this document:

Customisable Straight (and Pivot) Tables - more food for thought

Re: Execute different expressions on a chart using macro

marcus_sommer — Tue, 03 May 2016 09:11:01 GMT

To use user-defined functions within qlikview objects you would need to enable them within the easter eggs - whereby those configurations aren't supported from qlik and are generally not recommended for using. And even with this won't be an eval() work then the result will be only a string for qlikview and not an expression and you would again need the $-sign expansion which couldn't be applied to get different results on row-level.

Therefore the only solution is to create an expression-chain with if-loops respectively significantely easier to create and better performing a pick-nesting. It's not really a beginner-stuff but not too complicated for use and mostly it worked quite smoothly and performant - whereby it will be of course quite slow if you want to apply it on a very atomic level with huge amounts of data. But on this level it makes not much sense - really useful might it be on higher consolidated data. Therefore I suggest you followed the suggestions from Gysbert.

For others to get a better impression about what we talk, see: Dynamic expressions without Pick.

- Marcus