{"id":99,"date":"2018-02-25T08:03:11","date_gmt":"2018-02-25T08:03:11","guid":{"rendered":"http:\/\/qlikwithfriends.com.temp.realssl.com\/?p=99"},"modified":"2018-11-25T08:06:10","modified_gmt":"2018-11-25T08:06:10","slug":"using-aggr-instead-of-if-for-complex-dimensions","status":"publish","type":"post","link":"https:\/\/qlikwithfriends.com\/index.php\/2018\/02\/25\/using-aggr-instead-of-if-for-complex-dimensions\/","title":{"rendered":"Using AGGR instead of IF for complex dimensions"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

In this post, I\u2019ll focus on a quick tip that encourages the use of Aggr for complex dimensions.<\/span><\/p>

\u00a0<\/span><\/p>

What\u2019s a complex dimension? Most of the time, we\u2019ll construct the database for our app so that our users can simply drag or select a field for a dimension and use it\u2014that\u2019s a simple dimension. Sometimes, however, we\u2019ll need to use a formula for a dimension. This usually occurs when we need to apply filtering logic to the dimension, format the dimension (for example, a date format), or create a custom string for a dimension\u2014these are examples of complex dimensions, and depending upon the situation, they can get very complex.<\/p>

\u00a0<\/p>

When you select a simple dimension in a chart or table, Qlik Sense will show the selection you made in the filter bar. For example, I we selected the value \u201cUS\u201d for the dimension Region, we\u2019d see something like this\u2026<\/p>

\u00a0<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

But what about a complex dimension? When you use a formula as a dimension, Qlik Sense does a good job of figuring out how to determine what actual fields (dimensions) are being filtered, but in many situations, it can\u2019t. In those cases, instead of seeing one or more dimensions in the filter bar, you\u2019ll see a formula. While technically correct, this can be confusing, especially to end users.<\/p>

\u00a0<\/p>

Here\u2019s an example.<\/p>

\u00a0<\/p>

I made a simple table that displays information for film actors and actresses who have been nominated for academy awards. I\u2019m using a simple dimension \u201cDisplay Name\u201d for the table.<\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

I’ll select a group of names.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

We\u2019ll see the dimension name and number of selections displayed in the filter bar at the top of the screen.<\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Now let\u2019s look at an example of a more complex dimension. I want to change the \u201cName\u201d column to show formatted text\u00a0<\/span><\/span>comprised<\/span><\/span>\u00a0of the Display Name and whether the person is an actor or actress. I\u2019ll change the dimension to use the formula below.<\/span><\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
=[Display Name] & If(Gender = 'M', ' (actor)', If(Gender = 'F', ' (actress)'))<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Here is the result.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Let\u2019s make the same selection that we did earlier.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Notice what has happened in the filter bar (below). Qlik Sense displays the formula we used instead of the dimension \u201cDisplay Name\u201d. If you hover over it, it will actually display the entire expression that was used.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

We actually did select those Display Names and if the dimension appears in a Filter Pane object, it will correctly show this. But, the information shown in the filter bar at the top of the screen is confusing, because it doesn\u2019t really show this.<\/p>

\u00a0<\/p>

Fortunately, we have another option. Let\u2019s change the formula to use an AGGR function. The resulting expression will look like this.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
=Aggr([Display Name] & If(Gender = 'M', ' (actor)', If(Gender = 'F', ' (actress)')), [Display Name])<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

This might look confusing at first, but all I\u2019ve done is use the same expression as the expression in the Aggr function and used the dimension as the grouping dimension in the Aggr. This produces the exact same set of dimension values and my table will look exactly the same.<\/p>

\u00a0<\/p>

If I make the same Name selection again the filter bar will now show the dimension name \u201cDisplay Name\u201d instead of the expression.<\/span><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

This is\u00a0exactly<\/u>what we want and will work in almost all cases, regardless of how complex my Aggr statement is. Using Aggr is a flexible way to ensure that your calculated dimension will correctly display in the filter bar.<\/p>

\u00a0<\/p>

If you\u2019d like to understand more about what you can do with Aggr, there are many examples on QlikCommunity and in the Qlik documentation.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

In this post, I\u2019ll focus on a quick tip that encourages the use of Aggr for complex dimensions. What\u2019s a complex dimension? Most of the time, we\u2019ll construct the database for our app so that our users can simply drag or select a field for a dimension and use it\u2014that\u2019s …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[6,8,7],"tags":[],"class_list":["post-99","post","type-post","status-publish","format-standard","hentry","category-qliksense","category-quick-tip","category-visualizations"],"_links":{"self":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/99","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/comments?post=99"}],"version-history":[{"count":3,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/99\/revisions"}],"predecessor-version":[{"id":111,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/99\/revisions\/111"}],"wp:attachment":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=99"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=99"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=99"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}