QlikSense Set Analysis – Creating and Using Variables

QlikSense Set Analysis – Creating and Using Variables

Patrick McCaffrey
Practice Director, Business Analytics
John Daniel Associates, Inc. 
Patrick’s Profile

The majority of Qlik application developers will tell you that a large portion of their time is spent doing rework for customers who consistently change the scope of work. This can be very frustrating, especially after hours of work have been spent placing business logic in numerous complex expressions and the customer comes to you and asks you for something as simple as the addition of a “Product Type.” To the customer this may not seem like a lot of work, but developers, even at a beginner level, know that the addition of a Product Type may require hours of additional work depending on how many expressions need updated to reflect this change. The truth of the matter is that the customer is correct – this should not be a lot of work! Throughout the remainder of this blog I’d like to give you an experienced developer tip that can save you tremendous amounts of time and headaches while creating or updating your set analysis expressions.

An experienced developer should plan ahead and predict what variables may frequently change throughout the development process based on their previous experiences during customer engagements. They use these past experiences to take a proactive approach when developing their set analysis expressions, which they do by implementing variables. The use of variables in set analysis expressions diminishes the need to constantly go back and modify each and every expression every time a change is requested.

Imagine that your customer, a sporting goods store named ‘XYZ Sports’, has just told you that they want an application that shows the sales of their current outdoor sports inventory, but they only want to see the sales that relate to basketball, football, soccer, and baseball. The image below shows a general set analysis expression you would use in this situation. In the image you can see that we are summing sales of ‘Basketball’, ‘Football’, ‘Soccer’, and ‘Baseball’ for the month of ‘July’ and the year ‘2016’.

XYZ Sports then tells you that they want to see the sales of these same Product Types, but they would like to see the sales over the entire year of 2015, the entire year of 2016, and each quarter of 2016, all of which need to be in a different chart. You would now have a total of 7 expressions that include the Product Types field in your application. A few examples of these expressions are below.

A week after you have the application built and all expressions working for these Product Types, the Project Manager at XYZ Sports comes to you and explains that they are now considering ‘Hiking’, ‘Fishing’, and ‘Camping’ as outdoor sports and this inventory will also need to be counted towards the sales in the charts you have just created. The first thought from many novice developers is to go back into each expression and make the requested changes to the ‘Product Type’ field as shown below.

Updating all 7 charts with the new “Product Type” fields as shown above is a solution, but it is not the most effective solution. However, the use of variables will allow for this change to be made in one central location, which would apply the changes to all expressions containing that variable. In order to do this you would need to follow the steps below:

Step 1:

Open the Variable Overview and create a Product Type variable.

  • In QlikView press Ctrl+Alt+V
  • In Qlik Sense Hit Edit then click  at the bottom left of the screenIn the variable, place the entire string that is expected between your two Product Type curly brackets, or braces.

Step 2:

In the variable, place the entire string that is expected between your two Product Type curly brackets, or braces.

Product Type = {  }

Do not include an ‘=’ in front of the variable, as it will cause issues with the format in the next step (Step 3). Depending on your variable format, it may look as if you have spelling errors due to the red underscores, but you can ignore this warning.

Step 3: 

Place the newly created variable in your set analysis expressions in the format shown below. This variable will input the entire string you created in step 2 above.

Now each time you need to change, add, or remove a ‘Product Type’ you simply have to go to the Variable Overview screen and make the modification in that one central location. Once the change is made in the Variable Overview screen all expressions containing that variable will be updated. You no longer have to go into each expression and make the changes one at a time! This same approach can be taken for ‘Sales Month’, ‘Sales Year’, or any other field that you feel will be consistently changing throughout the front end development process.

  • It’s important to note that variable usage is much more practical when you have numerous expressions using similar fields and values throughout your application. Because of this you should evaluate your set analysis approach on a case-by-case basis.
  • Sometimes the need for a variable is evident from the onset of the application build which makes it easier to plan ahead and use variable implementation from the start. Other times you may not see the need for a variable until you are well into the development process. Identifying these scenarios will come with practice and experience.

I can already hear some of you say, “But, Master Measures in Qlik Sense….” Yes, Qlik Sense has helped to alleviate some of this pressure when utilizing the same expression over multiple charts with the introduction of Master Measures, but many Master Measures may still have some Set Analysis in common. The use of variables in those cases will accomplish the same thing. If that Set Analysis ever changes, you can simply update a variable for all, rather than adjust each one individually.

Front end application development can be tricky, cumbersome, and time consuming, and while variable usage can be a tremendous help in remedying these issues you will learn that there are many other tricks and tips that will help you become a more advanced and efficient developer. Developers should consistently be looking for ways to sharpen their skills and make their own work more efficient. No matter what level developer you are, I suggest that you do the same. Taking past and current experiences, learning from them, and building upon your current skill set is a great way to increase your efficiency in future endeavors. Knowing and understanding how customers operate can keep you a step ahead of the game while saving invaluable amounts of time, as evidenced above by the use of a simple variable. Process improvement skills, such as effective variable use, are highly valuable in the business intelligence field and will be acquired over time as you gain more experience.

 

 

Patrick is our Business Analytics Practice Director, a valued member of our John Daniel Associates Leadership Team in Pittsburgh, PA.

Save