Hello all! It’s been a while since my last post, so here I am trying to catch up with you guys again…
In the past few months, I came across a couple of functions which I hadn’t used before. Discussing these with Barry, he mentioned that Rob Wunderlich has done a Survey among QlikView Developers and created a list with functions and the frequency of them being used. Looking at that list, I will try to clarify what some of the functions do in a clear and easy example. Also, I will – hopefully together with you – try to figure out why the hack they’re positioned that low!
So, this may seem a bit of an incoherent collection of functions, but it’s just based on my own experience. This is no guide to the ultimate use of anything, just a couple of nice insights (or, at least, I hope so).
In this post, I will walk through some front-end functions. No need to worry though, I’ll get back on script-functions in a later post. Here we go!
Dimensionality() (# 49 on Rob’s list)
I ran into this function when the need was there to perform a different calculation on the total-row than on the individual rows in the table.
Imagine data for items: # sold and # returned. To calculate the % of items returned, we’d just divide the two columns. However, if there is a large difference in amounts, that same calculation wouldn’t say too much on the total level. If you add an expression: Dimensionality(), you get to see the various levels in your table. See the example file how to calculate a weighted average using dimensionality() and aggr().
Another use of the dimensionality() function, as shown in this blogpost, is to use it to format cells depending on the level you’re looking at.
OSUser() (# 78 on Rob’s list)
Another thing I found out is that security might be taken care of on various levels. For instance, a document can be in a folder that is only accessible to a few, or section access is used. But, if you only want to hide certain sheets or objects from people, you might want to use a show-condition (on the tab layout) in these items using OSUser().
The OSUser (Operating System User) returns the name of your user-account. One way to find your user account is to press the Windows-button + R (Run) and run CMD. This opens up the command prompt. If you type in ‘whoami’, the name of your computer or domain and your username are returned. In my example, the user is called ‘Wesley’. So, if for some reason, I only want an object visible for me, I fill in the show conditional function as:
I used Wildmatch() and *Wesley to cover my user-name in multiple domains (for example if you have the same username in both a test- and production environment. Better and more secure would be to type in the full domain and name.
You can see an example of this in the example file… Oh Wait! You can’t! I’ve just hidden it from you! Well, maybe a little trick will help: hit: CTRL +Shift + S; this will show all hidden objects!
The last bit might be new to you. Or not, if you read this earlier post. Anyways, make sure to undo the CTRL + Shift + S part if you’re done developing – you do want your show conditions to work properly, right?
Also, this last bit shows that this isn’t the most secure way to hide information from certain people. A more secure way is to use section access, but I found this to work fine (and quick) when developing and testing with end-users.
GetCurrentfield (# 46 on Rob’s list)
Yet another function that’s on Rob’s list and which I came across, is GetCurrentfield(). While building a dashboard in which a cyclic group came back quite often, it came in pretty handy.
The situation before was that there were 3 kinds of tables, one for yearly, one for monthly and one for daily views. Now of course we want to use a drill-down or a cyclic group here, to simplify all that and save space. The wish was to keep clear what people were looking at. Now of course, this can be achieved in multiple ways. One of them, which I found quite elegant, is to show the current field in the group in the caption of the chart by setting the caption-text as something like:
Et voilà! Clarity all around!
Well, that’s it for now. I hope this blogpost gives you some new ideas to improve your dashboards, Also, if you’re already familiar with the use of the functions I named, I’d love to hear your stories or perhaps better alternatives?
See you next time, when I’ll go a bit deeper into script functions.
You can download the example application through the link below.