Hello, and welcome back to the Q-Tips Blog! Our two wonderful Masters Summit events are over, and I’m back to share some of the new nuggets of cool stuff that I learned and discovered in my conversations with colleagues and with our students. What I love about our live Masters Summit events is the unique opportunity for knowledge sharing. We teach a lot, but we also learn so much from every interaction! Even in this new post-pandemic world, when we’ve gotten used to working, teaching, and learning remotely – even enjoying music performances remotely – there is still no adequate replacement for old-fashioned ways of getting together and enjoying each other’s company face to face. So, stay tuned for our advertising for next year’s live events!

Having said that, we know that there are people that can’t or won’t travel to attend a live event, for a variety of reasons. In order to enable these Qlik developers that seek advanced knowledge, but can’t travel, we decided to run a series of online lectures with some of the best content from the Masters Summit.

I Have to Digress…

Let me just give you a little flavor of what to expect.

1. First, on February 15th, Barry Harmsen (the author of the very first Qlik book QlikView for Developers) will start the series with Data Modeling. Barry will take you through advanced techniques, such as Link Tables, Concatenated Facts, Slowly changing dimensions, the As-Of-Date Table, and more.
2. Next, on March 1st, Oleg Troyansky (your humble servant and the author of QlikView Your Business) will teach Set Analysis and Advanced Aggregation. I will take you through numerous creative analyses, based on advanced uses of Set Analysis and AGGR. If you had any confusion about these techniques, I will demystify them one by one. Pour your favorite coffee drink and buckle up!
3. On March 15th, Bill Lay will talk about Effective Visualizations – how to apply best practices, how to avoid common pitfalls, and how to tell the story the best way – you’ll see by Bill’s teaching style that he is not just a teacher, but a prolific teller of stories and anecdotes!
4. On March 29th, I will be back with the Performance Tuning session. You will learn the best practices of good performance, the common issues that cause bad performance, and how to use the Document Analyzer (which is now called QSDA Pro) to identify performance issues in your apps. You will also receive a temporary QSDA Pro license that will allow you to analyze performance and to optimize your own applications.
5. Finally, on April 12th, Nick Webster (a brilliant web developer and the technical director at Websy) will introduce you to the fascinating world of Qlik Sense Integration.

Visit MastersSummit.com to register!

While the news about our online sessions can be very exciting for many, this is not the main Q-Tip in this article. I wanted to talk about Fields On The Fly, and about some of their very cool capabilities, along with their not-so-cool impact on performance. Let’s peel the onion, one layer at a time.

Fields On The Fly – What Are They?

Fields on the fly are not new, they existed for a long time. Henric Cronström blogged about them (here) back in April 2020. What might be new, at least for me, is my understanding of how and where they can be used – and also why many times they shouldn’t be…

In a nutshell, every time we use a formula in place of a database field, Qlik Sense will generate one of the two flavors of Calculated Fields. Field on the fly appears to be the better flavor of the two – it results in the engine adding another column in the data model, and the new “field” is treated just like any other data field in your model. The condition that determines this choice is that your formula has to be relatively simple – it can only be based on a single field, or on multiple non-key fields from the same data table, and it cannot contain any AGGR() functions in it. If this condition is not met, then Qlik has to resort to an old-fashioned Calculated Dimension, which is handled differently, performs differently, and speaking in technical terms, isn’t as cool overall.

So, any time we use a formula in a Master Dimension, or in any Chart Dimension, for that matter, – if our formula is simple enough, another Field-On-The-Fly gets generated and added to the data model behind the scenes. Pretty cool, huh? Well, let’s talk a little bit more…

Fields On The Fly and Set Analysis

You may have known this for years, but I just recently learned that FOTF can be used in Set Analysis in place of a data field, just like this:

I mentioned this feature in my previous blog post, where we talked about subtle differences between Simple Search and Advanced Search in Set Analysis. This syntax is useful in case you need to avoid using Advanced (aka Expression) Search in the Element Set (that’s the official name for the stuff we put inside the curly brackets { … } to define our desired selection.)  In a similar way, you can use the InYearToDate() function to avoid creating calendar flags in your data model. Perhaps you can come up with your own creative scenarios, where a calculated formula could be sensitive to user selections and therefore be more useful than a static field. Please post your ideas in the comments section below.

Fields On The Fly and AGGR()

Next I asked myself – if so, would it be possible to use FOTF as AGGR() Dimensions? Something along these lines:

AGGR(
     Sum([# Amount]),
     "=Year(Date)"
)

In fact, someone asked me this question months ago, and my quick “off the hip” response was “nah, that can’t be possible!”. However, after seeing how it works in Set Analysis, I decided to test it (using Qlik Sense August 2022) and much to my surprise, it worked! Not only can we sort our AGGR() dimensions by expression (did you know that, by the way? You would, if you listened to my Set Analysis and Advanced Aggregation session at the Masters Summit!), but now we can even use calculations in place of a field in the Dimension of the AGGR function! How cool is that?

Fields On The Fly and Why They May Not Fly

And now, boys and girls, it’s time to be completely honest. Fields on the fly might look cool, and they can save you a bit of extra scripting effort, but generally speaking, they are not good for you. They may cause severe performance issues, and in extreme cases, they can bring your servers down.

The problem is that every time you decide to use a FOTF instead of adding a real field to your data model, Qlik will have to generate that field for you, literally “on the fly”. As you know, “on the fly” is  that special time when you have an impatient user in front of the screen, nervously drumming on his desk while waiting for the dashboard to open and to show him if he can go play golf or if he needs to have uncomfortable conversations with his employees or, even worse, with his management. So, by adding fields on the fly, we are making his waiting time longer, and he might miss his tee time.

Remember how we decided long ago to replace costly IF() conditions with conditional flags, which we calculated in the data model, to save time “on the fly”? Well, using FOTF in place of conventional database fields is sort of the opposite of that. We add time “on the fly” to save us extra effort of adding these fields in the data model.

So, what are these FOTF good for, anyway? Their calculations are limited to a single field, or to a few non-key fields from the same table – that means that we can ALWAYS replace them with conventional database fields. Unlike the AGGR function (another cool feature that should be used in moderation), FOTF are not entirely necessary for any analytical needs. I couldn’t come up with a legitimate cause that would warrant using FOTF instead of adding a conventional field, but if you can think of one, post your idea in the comments!

I’d say, using FOTF could be a good prototyping technique – sort of like “try before you buy”. You can try certain solutions “on the fly”, before spending the time and adding new fields to your data set.

It can also be useful to you if you don’t have access to the data load script. In this case, adding an occasional FOTF could save you a round trip to your data architects, which could be unpleasant in some cases.

However, whatever you do, remember the golden rule of Qlik Sense performance (that I borrowed from Aristotle) – everything is good in moderation! Don’t create too many FOTF, don’t use heavy complicated calculations, and every time you can, go back to your data models and replace your FOTF with conventional database fields.

And then – enjoy the upcoming Holidays, and check out the schedule of our online Masters Summit at Home. If you can’t attend our live events, the online event is a close second choice!