Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As a new-er member of Qlik’s demo team, I am often learning new approaches to building expressions and visualizations when creating apps. Currently I am working on a college football app that uses a variety of expressions to process the data we’ve loaded. Here I will explain some of the techniques that I have used to troubleshoot my expressions.
In our college football app, we’re bringing in a lot of data from a lot of different places, matching them up, adding them, multiplying, dividing, all manner of things to get the information we need. The primary goal of this app is to find ‘The Greatest College Football Team’. The ‘greatness’ of each team was determined by a combination of factors that combined into ‘Total Points’, the team with the highest amount of points, would be crowned the greatest.
To find these ‘Total Points’, we have a formula:
That’s not the longest expression that you will find, you might have created some longer ones yourself, but what are these pieces to this expression? Each portion of this expression is created by a combination of data that we’ve loaded into our app, let’s look at one of them:
Now we’ll get into creating and testing our expression. First, we’ll select a team we’ll be finding the values for, with this example we’ll use 2019 LSU. To ensure that I have the right measures, I create a new sheet in my app, then KPIs for each value in my expression; Total Offensive Yards Per Game, Points Per Game and Offensive Turnovers. This is to ensure I am working with the correct values. Additionally, I add a filter pane to my sheet to select my team.
My Sheet now looks like this:
We’ll now double check our values against our source data, and we have a match. Now let’s combine these values into an expression. Add another KPI to sheet, click Add, then the fx to being creating your expression. We’ll now arrive at our expression editor. I often find it helpful to open a second window of your app so you can reference back to KPIs to ensure you’re collecting the correct values.
Now we’ll add our values into the expression in the form of our original equation.
You may notice that these values are in different colors. The light blue/grey colors are master measures, where as the gold are fields directly from our data set. Make sure to include your parenthesis to control the order of operations in the equation. For our example, we want Total Yards and Points Per Game to be added together before being divided by Offense Total Turnovers, so we’ll put parenthesis around them.
Before selecting apply, ensure that Qlik OK’s your equation, this information can be found at the bottom of the expression editor.
Some of the best advice I can offer when troubleshooting an expression is to make sure you have an even number of parenthesis.
Here we have an extra parenthesis, and it is causing us to return an error in our expression. If we remove it, we’ll see that Qlik gives us the OK.
Now we’ll click apply and check the value we returned.
Our sheet should now look like this:
Looking back at our reference data, we’ll see that our expression is working as it should. Venturing back into ‘Edit Sheet’ we can click on the KPI for our expression and open up the measure to find this screen:
At the bottom, we’ll find ‘Master item’ and a button that says ‘Add new’. Click on that to open this menu:
We can now add a name to our expression and click create, turning our expression into a measure that we can use throughout our app.
Now we can do this for all of the sections of our Total Points expression, using the same technique to create the Defense Percentile, Special Teams Efficiency and S.O.S. Percentile, each of these becoming their own measures whose expressions will be below. Our sheet should again have the individual parts of our expression so we can double check the values.
Defense Percentile:
Special Teams Efficiency:
S.O.S. Percentile:
Our new expression will look like this:
Let’s start building. We’ll start with the three measures at the beginning.
Remember to add your parenthesis to group these measures and then refer back to your KPI to check that our values are correct.
Perfect! Now we can begin adding onto it. Until our expression is fully filled out.
Then we double-check our values, and then we can make this a measure as well!
Great! Now let’s look at our work, what have we accomplished. Through this process we have taken what would have been this equation:
Into:
A bit easier to work with. And we have checked our values along the way to ensure they are correct. With this, we can use this one small, easy to use measure all over our app without having to worry about a missed parenthesis, or an incorrect value.