Where to store your expressions in QlikView?

· by · in Best Practice. ·

Hello,

Writing expression in QlikView is pretty straight forward to do and these expression aren’t just limited to calculations either. They can also control the visibility and style of your object, anywhere you can see the three dot … button means you can write an expression.

So where best to store them. Those new to QlikView maybe unsure why I am even asking that question. Surely you just write what you need into the expression editor and that’s that?

Well during early development that is exactly what you will do, cutting and pasting the calculations between objects. Once your dashboard becomes large and you need to think more about future development and accuracy you will need use another option like storing the calculations into variables and using those in your charts. There are three reasons for doing this:

Consistency. Because you’re re-using the variable you’re not going to miss a small difference in a calculation tucked away in some chart you though should have been the same.

Accuracy. Expressions only need to be changed once for the entire dashboard to be updated. This minimises the risk of making an update in the future but missing that one calculation tucked away somewhere. Expressions stored in variables can also reference each other which also means if one is changed in the future the new results will filter down though all the other expressions that rely on that calculation.

Performance. Did you know if you have what is effectively the same expression but written slightly differently (even as subtle as an extra space in the expression) QlikView will parse them independently? Keeping expressions consistent in variables will stop this from happening.

There are downsides of course. Once the expression is saved into a variable you can’t quickly see what’s going on and you’ll find yourself creating a number of text boxes on screen referencing the variables so you can see their contents.

 

I prefer another solution. All my expressions are stored in a data island (a data tables which isn’t linked to the rest of the structure) and I generate this using a simple spread sheet.

  • I can add a lot more information such as the Label, Help Text and other developer notes and grouping which is only visible to me.
  • I can search the spread sheet and make sweeping changes.
  • I also create more expressions automatically during the load which links back to all the possible Point in Time flags I have in my As Of Calander.
  • The data island also drives my customisable charts where a user can select what ever dimensions and expressions they like from a list and the chart will auto populate.

I will come back to these additional features in future post.

 

Love to hear your comments

Richard

13 responses to “Where to store your expressions in QlikView?”

  1. Richard, how do I activate an expression from a data island? I know how to select the content from a column in a data island (translation table). How do I get the right calculation in my expression since there is no selection to be made of a row.
    Load the excel in columns (name, expression, …) and transpose this in the load?
    Thanks

    Like

    • Hi Michiel,
      Thanks for your comment.

      As a minimum you will have two fields. The first is a unique ID and the other is the expression itself.

      Then to return and evaluate the results your expression will read:
      =$(=only({1}%CC_Expression_Expression))

      I’ll aim to write a more detailed explanation in a future post.
      Thanks
      Richard

      Like

  3. Pingback: Storing Expressions In Variables | qlikcentral·

  4. Pingback: Storing Expressions Into a Data Island | qlikcentral·

  5. Hi Richard,
    There’s another advantage to this approach. In a couple of projects I’ve worked on, the client has required, that depending on the role of the logged in user, that some metrics should be unavailable. This method gives the developer the ability to remove metrics using data reduction. The other popular method of managing your expressions is by creating variables from a central spreadsheet at reload time. Once the variable is created though, there is no easy way to remove or obfuscate it depending on the role of the user.
    Nice work.
    George

    Like

  6. Hi Richard,
    There’s another advantage to this approach. In a couple of projects I’ve worked on, the client has required, that depending on the role of the logged in user, that some metrics should be unavailable. This method gives the developer the ability to remove metrics using data reduction. The other popular method of managing your expressions is by creating variables from a central spreadsheet at reload time. Once the variable is created though, there is no easy way to remove or obfuscate it depending on the role of the user.
    Nice work.
    George

    Like

  7. Hi, thanks for this tip! Are there any performance issue with using this data island technique for expressions comparing to storing the expressions in variables in an qvs file?

    Br
    Patrik

    Like

      • Thanks for your quick reply! I also read that suggestion but not sure how that would look like when you have your expressions in an dataisland. How would that work in your dataisland example above?
        Br
        Patrik

        Like

      • To be honest I’m not 100% sure without testing. The expressions just use set analysis so I don’t think you have to adapt them.

        Really though the alternate state would only be needed if you were using them in a customisable chart. If you are just using them to store expressions then you don’t have to worry about it at all.

        Like

Leave a comment