{"id":77,"date":"2018-11-05T05:50:22","date_gmt":"2018-11-05T05:50:22","guid":{"rendered":"http:\/\/qlikwithfriends.com.temp.realssl.com\/?p=77"},"modified":"2018-11-13T06:09:34","modified_gmt":"2018-11-13T06:09:34","slug":"off-the-charts-when-and-how-to-use-a-custom-axis-on-a-chart","status":"publish","type":"post","link":"https:\/\/qlikwithfriends.com\/index.php\/2018\/11\/05\/off-the-charts-when-and-how-to-use-a-custom-axis-on-a-chart\/","title":{"rendered":"Off the Charts. When and how to use a custom axis on a chart."},"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

Most of the time, when you create a chart, you can leave the Range selection for the X- and Y-axes on \u201cAuto\u201d. Recent versions of Qlik Sense have improved the logic that\u2019s used to determine these ranges and it\u2019s also usually the most efficient option in terms of refresh time\u2014and your time. But there are times when your requirements or your data characteristic don\u2019t fit the standard logic that the visualization engine uses to determine the ranges for axes on a chart. In these cases, you\u2019ll need to customize how the scale for the X-axis or Y-axis; or both, by using a custom formula.\u00a0<\/p>

\u00a0<\/p>

So, what situations require us use a custom formula for an axis? Custom Min\/Max values are most often set for the Y-axis, either when the choices for this range are not obvious or when you\u2019re using reference lines. This post focuses on the latter case.<\/p>

\u00a0<\/p>

The chart below shows the average weekly temperature in Manhattan in 2018. I\u2019ve added two reference lines\u2014one showing the overall average temperature (for all the data) and one showing the average temperature for the period shown (filters applied). Since I haven\u2019t filtered anything yet, both are the same. So far, so good.<\/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\u2019ll list the formulas used for the dimensions and measures for clarity and because I\u2019ll refer to them later.<\/p>

\u00a0<\/p>

Dimension:\u00a0[Week Ending Date]<\/span><\/i><\/p>

Measure:\u00a0[Weekly Temperature]<\/span><\/i><\/p>

Reference Line for Overall Avg:\u00a0Avg({1} [Weekly Temperature])<\/span><\/i><\/p>

Reference Line for Period Avg:\u00a0Avg([Weekly Temperature])<\/span><\/i><\/p>

\u00a0<\/p>

Here\u2019s another example when I\u2019ve selected a subset of the dates. Now we have different values for the two reference lines and the chart still renders the way we\u2019d expect. Still good.<\/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

The issue discussed in this post appears when I select a subset of the dates that causes the measures (the lines) that are displayed to be far from one or more of the reference lines. In this case the chart optimized the display for the normal range, but doesn\u2019t show the reference lines that fall way outside the range. Instead, it shows a small icon at the upper or lower right corner of the chart, depending upon the line that isn\u2019t shown.<\/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 look at how the chart uses our data to create the Y-axis values when we choose the \u201cAuto\u201d setting\u2014It\u2019s using the values of the data in the measures, but not the values for the reference lines This makes sense\u2014the display favors the main chart data\u2014but if the reference lines are also important then using \u201cAuto\u201d is probably not the best option.<\/p>

\u00a0<\/p>

There are two approaches to handling this solution. Both have a different visual result.<\/p>

\u00a0<\/p>

The first approach is the quickest and easiest. Simply click the checkbox for \u201cLogarithmic scale\u201d that\u2019s just below the \u201cRange\u201d selector.\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

Qlik Sense will always adjust the Y-axis to include the full range. That\u2019s good, but the values on the axis will not be evenly distributed and though the lines shown will be scaled to those values, the shape of the lines may not be as expected. You can see this effect in the sample below.<\/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 could be confusing to users\u2014especially when the visual presentation of the curve (or trend) is important. In this case, there is another approach that will solve this issue. It requires that we explicitly set the formula that the engine will use to calculate the scale and values for the axis.<\/p>

\u00a0<\/p>

\u00a0<\/p>

First, let\u2019s look at the result (below). Compare it to the example above. Both are correct, but the second one presents a much more natural-looking curve and a Y-axis with evenly-spaced values. This is closer to what a user would expect.<\/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

Here\u2019s how to accomplish this. First, toggle the \u201cRange\u201d selector to \u201ccustom\u201d. This will allow us to select a custom formula for the \u201cMin\u201d, \u201cMax\u201d, or both. In this example, I\u2019ve selected the \u201cMax\u201d. I\u2019ll let Qlik Sense set the \u201cMin\u201d automatically.<\/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

Then, enter the formula to use for the \u201cMax\u201d value. I\u2019ve shown the one used on this chart.<\/p>

\u00a0<\/p>

\u00a0<\/p>

=Ceil(<\/p>

\u00a0 \u00a0 \u00a0RangeMax(<\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Max(Aggr(Avg([Weekly Temperature]), [Week Ending Date])),<\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Avg({1} [Weekly Temperature]),<\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Avg([Weekly Temperature])<\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0), 10)<\/p>

\u00a0<\/p>

\u00a0<\/p>

Let\u2019s take a look at a generic version of the formula used.<\/p>

\u00a0<\/p>

\u00a0<\/p>

=Ceil(<\/p>

\u00a0 \u00a0 \u00a0RangeMax(<\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Max(Aggr(the measure on the chart<\/span><\/i>,\u00a0the dimension for the X-axis<\/span><\/i>)),<\/p>

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0the formula used for the first reference line<\/span><\/i>,<\/p>

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0the formula used for the second reference line<\/span><\/i><\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0), 10)<\/p>

\u00a0<\/p>

\u00a0<\/p>

The formula takes the highest value of the measure (Y-value) on the chart<\/p>

\u00a0<\/p>

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

\u00a0 \u00a0 \u00a0 Max(Aggr(the measure on the chart<\/span><\/i>,\u00a0the dimension for the X-axis<\/span><\/i>))<\/p>

\u00a0<\/p>

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

Then it takes the maximum value (RangeMax) of that, or any of the reference line formulas<\/span><\/p>

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

\u00a0 \u00a0 \u00a0 RangeMax(<\/span><\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Max(Aggr(the measure on the chart<\/span><\/i>,\u00a0the dimension for the X-axis<\/span><\/i>)),<\/p>

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0the formula used for the first reference line<\/span><\/i>,<\/p>

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0the formula used for the second reference line<\/span><\/i><\/p>

\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 )<\/p>

\u00a0<\/p>

\u00a0<\/p>

And finally, it rounds that up to the nears increment of 10, using the Ceil function. You can change that increment depending upon the increment you\u2019d like for the upper value, or if you\u2019re using a percent scale.<\/p>

\u00a0<\/p>

\u00a0<\/p>

The advantage of the second approach is that you ensure that all of the reference lines appear on the chart and that the Y-axis and the lines are normally proportioned on the chart.<\/p>

\u00a0<\/p>

\u00a0<\/p>

Which approach is best? It really depends upon your requirements, your data and what the expectations of your users will be. Try this on your charts. You will probably find situations where your dataset is so large that using custom axis values is inefficient. That might be a limiting factor, or it might mean that you need to optimize your data model\u2014but that\u2019s a topic for another post.\u00a0<\/p>

\u00a0<\/p>

\u00a0<\/p>

If you need examples of how to scale the \u201cMin\u201d value, or how to do this for an axis that shows a percent please post in the comments.<\/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":"

Most of the time, when you create a chart, you can leave the Range selection for the X- and Y-axes on \u201cAuto\u201d. Recent versions of Qlik Sense have improved the logic that\u2019s used to determine these ranges and it\u2019s also usually the most efficient option in terms of refresh time\u2014and …<\/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,7],"tags":[],"class_list":["post-77","post","type-post","status-publish","format-standard","hentry","category-qliksense","category-visualizations"],"_links":{"self":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/77","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=77"}],"version-history":[{"count":4,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":97,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/77\/revisions\/97"}],"wp:attachment":[{"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qlikwithfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}