topic Re: Bar Chart Sort Order - Histogram Buckets in QlikView

Bar Chart Sort Order - Histogram Buckets

demarest — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to use a bar chart to create a histogram. I am struggling to get the buckets to order correctly. In summary, here is what I have:

I have tried every alternative in the sort options and not of them do anything. I am retrieving my data from SQL Server OLE DB using a stored procedure and have tried sorting the data there, increasing and decreasing the number of buckets, converting the numbers into varchar, etc. Here is my entire data set (the last two buckets are showing in between the 8th and 9th bucket in the wanna-be histogram):

fundName	bucket	frequency
FundA	-1.39	1
FundA	-1.28	0
FundA	-1.17	0
FundA	-1.06	0
FundA	-0.95	0
FundA	-0.841	0
FundA	-0.731	3
FundA	-0.621	2
FundA	-0.511	7
FundA	-0.401	7
FundA	-0.292	12
FundA	-0.182	28
FundA	-0.072	46
FundA	0.038	53
FundA	0.148	28
FundA	0.257	22
FundA	0.367	11
FundA	0.477	11
FundA	0.587	2
FundA	0.697	2

Here is what I put into the Data Load Editor:

LIB CONNECT TO 'sql2012prod';

histo:
Load *;

SQL EXECUTE [Analytics_Staging].[dbo].[spcd_dailyreturnhistogram];

All I want is for the buckets to show in the chart in ascending order. Any help would be appreciated.

Re: Bar Chart Sort Order - Histogram Buckets

rubenmarin — Thu, 23 Mar 2017 20:25:52 GMT

Hi Charles, in the right panel there is a 'Sort' section, expand the bucket dimension and try setting to sort numerically->ascending.

Re: Bar Chart Sort Order - Histogram Buckets

demarest — Thu, 23 Mar 2017 20:35:19 GMT

Thanks for the reply. The reason for this post is that I tried all of (1) Sort by Expression (2) Sort Numerically (3) Sort Alphabetically (after changing the SQL to return varchar) for "bucket". None of these have any impact on the chart, even if I switch from Ascending to Descending or do not select anything. (you can see this in the first image if you scroll right). Note however, if I sort by Frequency then the chart sorts, ie:

which is not what I want, I want the Horizontal Axis , "Return Bucket (%)" to be in ascending order. Any other ideas?

Re: Bar Chart Sort Order - Histogram Buckets

demarest — Thu, 23 Mar 2017 20:45:54 GMT

Hi Ruben,

I figured out how to make it work, even if nothing is checked for sorting Frequency like this:

Qlik will not respect the sorting for Return Bucket. But once I put Return Bucket ahead of Frequency then it works. This now works:

Re: Bar Chart Sort Order - Histogram Buckets

rubenmarin — Fri, 24 Mar 2017 07:38:27 GMT

Hi Charles, as you did, Return Bucket should be the first, double-check if you have to sort numerically instead of alphabetically.