topic Re: Dollar sign expansions removes dimensions in QlikView

Dollar sign expansions removes dimensions

Fri, 05 Aug 2011 18:56:09 GMT

Hi,

I'm trying to display a straight table of calculations where each row displays a separate calculation. I loaded a table with the calculation name and expression. In the straight table, I have the name of calculation as the dimension and use the dollar-sign expansion, $(=field_name), on the expression to display the value. The problem seems that the dollar sign expansion ignores the dimension; I've added the maxstring function to test and it confirms that the dollar sign expansion does ignore the dimension.

Does any know if there's a problem with my approach or if there is a workaround (short of if statements in the expression)?

I've attached a test qvw that shows the problem.

Thanks.

Dollar sign expansions removes dimensions

Fri, 05 Aug 2011 19:27:11 GMT

Hi there, I tried in the past to do a similar thing as you do now, however, I did not find a way to make qlikview evaluate correctly each formula according to each dimension change and I do not think it is possible. So, what I did is use a "pick match" type expression as the following:

pick(match(Calculation,'add','divide','multiple','subtract'),'sum(a+b)', 'sum(a/b)','sum(a*b)','sum(a-b)')

Notice that the expressions are wrap in single quotes so qlikview renders them as string, if you actually want to evaluate the formula just remove the string. Also, the formulas can be stored in variables for reuse.

Regards

Re: Dollar sign expansions removes dimensions

johnw — Fri, 05 Aug 2011 19:50:12 GMT

Correct, dollar sign expansion is done outside of the context of your dimensions.

It seems there should be some way to force QlikView to evaluate the contents of the field as an expression without leaving the context of your dimensions. For instance, it seems like the evaluate() function should be allowed in this context. But that function is very specifically not supported in charts for some reason, so it doesn't work. I've not found anything that works.

The workaround I know is essentially the one Ivan mentions. However, you CAN still use your current data model with the formulas stored in fields, and then generate the pick() from this data model. You can also generate a calculation sequence number in the script to avoid needing to do the match() at presentation time. See attached for an example of this approach.

Edit: For people with personal edition, here's the relevant script. Then you use Metric as a chart dimension, and $(vMetrics) as a chart expression.

Metric:
LOAD recno() as MetricSequence,* INLINE [
Metric, Expression
Count, count(Amount)
Max, max(Amount)
Total, sum(Amount)
];

Metrics:
LOAD concat(Expression,',',MetricSequence) as AllExpressions
RESIDENT Metric;

LET vMetrics = 'pick(MetricSequence,' & peek('AllExpressions') & ')';

DROP TABLE Metrics;

Re: Dollar sign expansions removes dimensions

llauses243 — Fri, 05 Aug 2011 23:03:33 GMT

Hi,

This is my offer for you, pls to see image adjust

No to use apostrophe ¡

Good luck, Luis

Dollar sign expansions removes dimensions

Sat, 06 Aug 2011 00:43:55 GMT

Thanks for the suggestions. John's solution is a bit easier to add/remove rows and columns of expressions (the end result is larger matrix of calculations).

Re: Dollar sign expansions removes dimensions

Anonymous — Thu, 03 Apr 2014 15:32:52 GMT

this post is an eye opener - i've been struggling with my expression for days. While it works when I manually select a dimension value, it would not work properly and would not pick up dimension values in expression. If you think about it though, I guess it makes sense because QV needs to know upfront the expression to use on a chart. pick/match does not work for me unfortunately because in my case it will 50000 characters expression

Re: Dollar sign expansions removes dimensions

Thu, 20 Aug 2015 14:54:41 GMT

Boris,

I had the same issue and unfortunately my list of expressions was huge. But they were grouped and that is how I needed to display them (Group 1, Group 2, Group 3...).

So I have my Category Field that I use to select the group I want to display. So I added a trigger to this field, to populate the vMetrics variable every time a new (and only one) value is selected, sorting them as well. Also,the first value on of pich() function is $1. So for example, my variable will be like this when I selected the Group1.

pick($1, Group1-Exp1, Group1-Exp-2, Group1-Exp3...)

Now, on my chart table, my expression is:

$(vMetrics(RowNo()))

This way I manage to reduce the number of expressions used on pick() function, and dynamically I could use the rowno() function to get the correct expression from the group I'm displaying.

Hope it helps anyone who had the same problem.

Regards

Re: Dollar sign expansions removes dimensions

qlikmeup — Thu, 18 May 2017 15:11:11 GMT

Hello John,

THX - took me days to find this solution 🙂