Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andydietler
Partner - Creator
Partner - Creator

7,448 Character Limit in Qlik Sense Expressions

There seems to be a limit of 7,448 characters in an expression field in Sense. It won't let you type characters after that. Is there any way to increase that, or plans to fix this limitation in future versions of Sense?

7 Replies
Gysbert_Wassenaar

Perhaps you should consider using variables for (parts of) the expressions if your expressions gets that long.


talk is cheap, supply exceeds demand
andydietler
Partner - Creator
Partner - Creator
Author

Unfortunately the latest production version of Qlik Sense 2.0.6.0 doesn't have the variable interface. Hopefully a 2.1.x version will be production ready soon.

Gysbert_Wassenaar

That's true, but you can create variables in the script in all versions. And there are also some extensions you could 'abuse' to create the variables.


talk is cheap, supply exceeds demand
andydietler
Partner - Creator
Partner - Creator
Author

True, that might have to be the solution for now, but that will be a little clunky as it's a long set of control rules with nested IFs so will be a mess to try and write out as variables in the script. I am all for making the code clean and succinct when possible and practical but I wonder what the reason is for the limit. Sometimes it's just a lot easier and better to leave the client with something they can easily read and parse if they need to make changes in the future.

marcus_sommer

Maybe your expression could be optimized. For example by replacing nested if-loops with pick(match()) functions. This meant sharing your expression could be helpful.

- Marcus

andydietler
Partner - Creator
Partner - Creator
Author

Script is this for a color code on a line chart. Could probably compact it in various ways, but in general I would like to be able to write out longer scripts and comment them when necessary.

//Western Rule #1 - One point lies beyond Zones +/- 3

If(

     Sum(Numerator)/Sum(Denominator)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      3*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     )

     OR

     Sum(Numerator)/Sum(Denominator)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      3*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     )

,red(),

//Western Rule #2 - 9 consecutive points lie on the same side of the center-line

If(

     If(Sum(Numerator)/Sum(Denominator)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),5)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),6)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),7)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     =8

     OR

     If(Sum(Numerator)/Sum(Denominator)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),5)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),6)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),7)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     =9

,red(),

//Western Rule #3 - 4 out of 5 consecutive points lie beyond the Zone +/- 2 (and on the same side of the center-line)

If(

     If(Sum(Numerator)/Sum(Denominator)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      +

      Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     >=4

     OR

     If(Sum(Numerator)/Sum(Denominator)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      2*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      2*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      2*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      2*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

      -

      2*Stdev(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     >=4

,red(),

//Western Rule #4 - 8 consecutive points lie one the same side of the center-line

If(

     If(Sum(Numerator)/Sum(Denominator)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),5)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),6)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),7)>

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     =8

     OR

     If(Sum(Numerator)/Sum(Denominator)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),1)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),2)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),3)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),4)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),5)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),6)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     +

     If(Above(Sum(Numerator)/Sum(Denominator),7)<

     (

      Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year]))

     ),1,0)

     =8

,red())

)

)

)

marcus_sommer

I agree that there should be no such small limit of chars within an expression but there are other limitations, too. For example the number of nested if-loops in qlikview (which are 99: Re: how many IF statement allowed in Qlikview script and if I remember correctly it's even more restricted within qlik sense - somewhere was a posting to this).

Although the if-loops couldn't be simply replaced with a pick(match()) logic I would change the expression then they is unnecessary long and complicated and could be optimized from a performance point of view. Like Gysbert suggested I would use variables for the heavily redundant expression-parts - very probably with parametrized variables like:

Variable: eExp

If(Above(Sum(Numerator)/Sum(Denominator),$1)

Expression-part:

... $(eExp(1)) ... // to get the result from the row above

and the expression: Avg(ALL aggr(Sum(Numerator)/Sum(Denominator),[Discharge Month Year])) would I calculate within a variable outside from the object then it's quite independent from the dimensions and will return allways the same result. This meant you need to calculate only ones and not several times within the object.

Further I suggest to remove unneeded brackets - it only confused and it's more error-prone - and to consider to replace the outdated ALL with a set analysis {1} and using from rangesum() instead of adding the various parts with the + sign (this saved no chars but it avoids potential problems with non numeric expression-results).

- Marcus