7 Replies Latest reply: Apr 19, 2016 3:21 AM by Marcus Sommer RSS

    7,448 Character Limit in Qlik Sense Expressions

    Andy Dietler

      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?

        • Re: 7,448 Character Limit in Qlik Sense Expressions
          Gysbert Wassenaar

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

            • Re: 7,448 Character Limit in Qlik Sense Expressions
              Andy Dietler

              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.

                • Re: 7,448 Character Limit in Qlik Sense Expressions
                  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.

                    • Re: 7,448 Character Limit in Qlik Sense Expressions
                      Andy Dietler

                      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.

                        • Re: 7,448 Character Limit in Qlik Sense Expressions
                          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

                            • Re: 7,448 Character Limit in Qlik Sense Expressions
                              Andy Dietler

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

                              )

                              )

                              )

                                • Re: 7,448 Character Limit in Qlik Sense Expressions
                                  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