A continuous X-axis, particularly in a line chart, is a great way to display certain types of trends in QlikView. For example, setting your x-axis to continuous is the only way to enable X-axis reference lines. In addition, if you have lots of distinct X-axis values, a continuous X-axis will let you display your data in a smooth way, without necessarily displaying each unique X-axis value. However, QlikView only provides the ability to sort your X-axis in ascending order. This article will describe how to "trick" QlikView into sorting your X-axis by descending numerical value!
Suppose you are trying to create a cumulative chart that neatly demonstrates the following principle in your business: the greater the sales threshold, the fewer customers we have that meet this threshold. You would, logically, expect your chart to look like this, where the X-axis represents sum(sales) in descending order, and the Y-axis represents the cumulative number of customers that have met or exceeded this sales threshold:
The above chart would demonstrate that you have few high-volume customers, but the lower you set your sales threshold, the more customers you include. Such a chart could be useful for determining sales quotas, etc. Unfortunately, anyone who has tried to build such a chart in QlikView has discovered that it is not possible to sort your continuous X-axis in descending numerical order.
The secret to overcoming this challenge in QlikView is to multiply your X-axis by -1, and then sort in ascending order!
Of course, as can be seen from the above, doing so will result in the axis itself displaying negative numbers. We cannot just create a "Text on Axis" expression where we show the absolute value of the X-axis, because we could have large gaps if we are missing data points (precisely what we are trying to avoid by having a continuous X-axis in the first place). To fully get around this problem and create a smooth distribution of X-axis values, it is necessary to utilize a straight table to create a fake axis, and to replace the default pop-up text with a custom pop-up label:
To see the solutions discussed in this article in detail, feel free to download the following QVW. And, as always, happy Qliking! XAxisDescending.qvw