Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dollar sign expansions removes dimensions

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

7 Replies
Not applicable
Author

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

View solution in original post

johnw
MVP
MVP

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;

llauses243
Creator III
Creator III

Hi,

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

No to use apostrophe ¡

Good luck, Luis

Not applicable
Author

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

borisman
Creator III
Creator III

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

Not applicable
Author

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

qlikmeup
Contributor III
Contributor III

Hello John,

THX - took me days to find this solution 🙂