Those of us old enough to have used QlikView will remember a feature that allows showing all dimension values in a chart, even if there were zeroes or no data for those values. In Qlik Sense, unfortunately, there is no such feature. After a bit of tinkering, though, I found a solution for this. And with Spring Training well underway, I thought a blog post referring to baseball would be appropriate.

Below, I’ve compiled all the games that my team, the Chicago Cubs, played in 2018. You’ll notice that the results are split into wins (blue bars) and losses (red bars). And just for the sake of pointing it out, the developers behind Qlik Sense are clearly Cubs fans, based on their selection of bar colors!

Now, let’s say I’d like to see how the Cubs fared against our biggest rival, the St. Louis Cardinals. When I select “STL”, as you will see below, months in which the Cubs did not face the Cardinals are suddenly not shown in the chart.

That would be fine, but my (imaginary) client required very assertively that we should always see all 12 months, no matter how many months have data and how many months don’t.

My first thought was: “Well, we have that setting “Show Zeros”, which I usually disable, while grumpily muttering to myself “Why would I want to see zeros by default in all my charts?” Here is the one business case when it can be handy. I check the setting. Nothing. Still not seeing zeros.

But then, if you think about it, these values are not zeros. They are just not there. Missing Values. Where is QlikView with its full set of settings like “Show all values”?

Then, I decided to tweak the Dimension in a way that would make it ignore user selections. With the help of my two best buddies, Set Analysis and AGGR, surely  I can create a Dimension that shows all values, no matter what was selected. Something like this should work:

            AGGR( {1} only(MyDimension), MyDimension)

The AGGR function loads the data directly from the data set, and the Set Analysis {1} condition requires all the data in the document, disregarding user selections. I plug the same formula into my chart:

Nothing. The chart ignored all my efforts to bring back all values.

Well, it’s time to work on the Measures. My two measures are two simple counts of the field “Result”, with simple Set Analysis filters that separated Wins from Losses. Time to dress them up in AGGR() and add another aggregation function on top:

sum({Set1}

    AGGR( count({Set2} MyCounter), MyDimension)

)

With two nested aggregation functions working together, I can play with the scope of aggregated data in a more advanced way than I could with a simple Count(). If I open the outer aggregation to the whole document, and keep the inner aggregation restricted to the desired values, I can get the winning combination of enabling all data and yet aggregating just what I need:

Voilà! The {1} in the outer sum() allows all data in, and the silent {$} in the inner aggregation ensures that only selected data is aggregated.

Now, when I go back to my sheet and select the Cardinals, the bar chart will display all months along the X-axis, including those for which data does not exist or renders a zero.

Interested in more techniques to optimize your data visualization? My Set Analysis and Advanced Aggregation session at the Masters Summit for Qlik will teach you creative ways to use Set Analysis, AGGR, and Alternate States for advanced analytical solutions. Interested in signing up? The next Masters Summit takes place April 1-5, 2019 in Stockholm, Sweden. There are still a few open seats left. We hope to see you there!