Conditional Columns

One of the big issues we deal with is the lack of space in our dashboards. We tend to busy-up things by squeezing too much into a limited space. Stop it! there's a better way.  It's like magic.

Qlikview 11 includes an easy way to conditionally show dimensions and expressions. In the expression box, you can make any magic happen. Use a variable or a user name. Show a column based on a selection in a list box. Basically, any expression that you can evaluate as true or false can be used in a conditional show. But we're going to get tricky.

Situation:

We have a proper scorecard with weighted categories that add up to an actual score. Since you are dashboard ninjas, you want to minimize your users effort by only showing reps and scores. No need to see detail if all of your reps are hitting their KPIs. But since there will always be lazy ineffective under performing reps, give your users an easy way to see some detail.

Here are the steps:

1. Create your table. Include all of the metrics that make up your score. 
2. Create an inline table with the names of your columns as the values.
3. Enter the conditions to show the expression.
4. Format and layout your creation.

Since we all know how to create a table, let's start with the inline table. An inline table is simply a few lines of text that let you easily add data to your document. Fortunately, there's a wizard.Hit Ctrl-E (or click on the script edit button) to open your script editor. Place your cursor at the bottom of one of the tabs. On the menu bar, go to Insert > Load Statement > Load Inline and you'll see the following screen (without the data):

It looks and acts like a flat Excel worksheet. I say 'flat' because formulas won't work; all you are doing is typing in a list. That list includes your column names from the table you've created. It's important that you put the column names in the same order as in the table. Click OK and you'll see the following:

Change F1 to a field name, in this case 'Scorecard'. Make sure that this isn't the same as any other field in the model. Above the LOAD * INLINE statement, enter a table name, in this case 'Scorecard' followed by a colon:

Click the Save icon. Click the Reload button. What you've just done is create a new field in your data model. Since it is not related to any other field or table, it's considered a 'data island'. That's OK because the only thing we are going to use it for is to control the conditional show of our table.

Create a list box based on your new field. (Marvel at your cleverness) We'll use this list box to show and hide columns in our table.

Next, open the Properties of your table. Click on the Expressions tab. Click on the first expression that you want to conditionally show and then check 'Conditional'. Open the dialog box. Enter the following expression:

     =GetSelectedCount(Scorecard.Scorecard) > 0
        and 
        SubStringCount( GetFieldSelections(Scorecard.Scorecard), 'Attainment') >= 1



While it looks complicated, if we break it down it's not too bad.

"GetSelectedCount(Scorecard.Scorecard)>0" says "look at field Scorecard.Scorecard and tell me if any thing is selected."

"SubStringCount( GetFieldSelections(Scorecard.Scorecard), 'Attainment')" says "if 'Attainment' is selected in Scorecard.Scorecard, then count the characters in 'Attainment'"

Put it all together and it says "if 'Attainment' is selected in Scorecard.Scorecard, show the column."

Repeat with each column you want to conditionally show, making sure to replace 'Attainment' with the proper column name.

Simple, right?

We make even more fanciness by making the list box look like the column headers. Format the list box to one row and remove the caption bar. Make sure that you sort by State and Load Order. Place it over your column header. Finally, size your columns to match your list box column widths.

The real magic is when you select your columns. Since you sorted the list box by State, the correct label will appear over the correct data:

Epilogue

I'm not sure that I love the presentation of this table, but I do love the ability to 'help' your users move through their data so that they can explore and solve problems. Be bold in your dashboards. Try new things and new ways of presenting data stories. And save backups just in case.