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
how would you make customize the x-axis to only show major points and have your plotted points fall anywhere in between? In other words, fabricate the points like a time scale if you wanted just the first day of each month on the x-axis but the actual days in between are plotted?
Tom, take a look at the application that I posted at the end of the article. I accomplished this with a straight table overlaid on top of the chart. If you look at the variables in the application, you’ll see the algorithms I created to define “major” points.
Regards,
Vlad
This is one route to get to this result, there are others I can conceive of that wouldn’t necessarily require table calculations but would involve duplicated data sources and more complicated filtering.
Does anyone have dealt with dates as x-axis?, I can’t find how to display them properly after turning them to negative.
Excellent question! To start with, there’s no such thing as a negative date, so you’ll need to tweak your thinking a bit. I would recommend creating an index for your date values, such that “1” corresponds to your highest date and ascends as your date values descend. Then create a regular continuous-axis chart (low to high) using this index as the dimension instead of your date field. Finally, hide the axis and display date values using the overlaid straight table solution in the above QVW.