I´m trying to create a pivot table that include 88 metrics each with their own unique expression. I was able to create the table for the first 20 or so metrics by assigning each expression to a different variable in the script and then using the dollar sign expansion and the pick function in order to execute the correct expression for each row.
However, after the first 20 or so metrics, all the other metrics would retrun null. If I separated the metrics that returned null into another table they would calculate correctly so my theory is that a expression in QlikView can only be so long and after so many caracters the expression fails to calculate. Somewhere around 10,000 caracters. Can anybody confirm this?
Has anybody else worked on a similar report format? I tried to make dynamic variable names so as to avoid having to expand all the expressions and use the pick, but I haven't had any success yet. Anybody else have a different idea?
The length of the expression seemed to be a problem in V9 SR1, but in V9 SR2 the pick expression that is far greater than 10,000 characters works perfectly fine. Some developer must have read my thoughts. Thanks.
I've only done something similar in a demonstration example. I had a three by three straight table with different calculations in each cell. I didn't use variables. I'll attach it in case it's any help. I haven't heard of a limitation on the length of expressions, but I haven't written any expressions that I would expect to hit a limit either.
It's possible that there's something wrong with the pick() rather than with the length of the expression.
I had a similiar problem. I initially had a working version which was completely hardcoded and didn't seem to have any problems with length. ie. If(Exp = 1,Sum(..), If(Exp=2,Sum(...) etc.
Tried to improve it by using variables created in script to define each calculation and substitituing in the expression but got same outcome, only worked for first 20 or so - then nulls. Splitting tables worked as with yours. I never bothered to count number of characters. Couldn't resolve it.
In the end changed the script so that pre-aggregated the Numerator and Denominator for each metric at plant / date level. Then the chart expression was simply Sum(Numerator) / Sum(Denominator).
The length of the expression seemed to be a problem in V9 SR1, but in V9 SR2 the pick expression that is far greater than 10,000 characters works perfectly fine. Some developer must have read my thoughts. Thanks.
