chart x axis custom sorting (sorting by another column in dataset)

Hi,
 
I have a chart in which I have months on x axis and measure on y axis and years as grouping.
 
In the Category(x series), I selected months abbreviation and in the sort, I selected month number. However, the chart is still not sorting according to month number
 
Basically, I have joined the months of my data with the months from calendar to show all months on x axis even if there is null data. I have two questions here
 
1) How do I hide the null from grouping? The nulls have appeared in the grouping/legend because I have brought months from calendar and there is no data for them. I want to hide those nulls from chart but still show the months on the x axis
 
2) What is wrong with my sorting. Please have a look at the attached images below
 
Arif

Find more posts tagged with

Comments

mwilliams

Did you try not joining with the calendar values and just deselecting the "is Category Axis" checkbox for your x-axis in the format chart tab? This would make it into a dateTime axis which would show all months between your data points.

Arif shah

HI mwilliams,
 
Many thanks for the reply. Actually I am fetching aggregated data at Year/Month level and do not have a datetime field in my dataset. I checked the x-axis option on the format tab and the "Is Category Axis" is disabled. Can you please tell me when is this option usable and when it is not?
 
P.S: I would still like to know how the sorting usually works when we are fetching empty months from Calendar table and also how to hide the nulls from y axis and groups. This can help me in situations where we need to join with other tables to fetch all series values even if measure table has no data for them
 
Arif

mwilliams

Can you create a sample showing what you're doing with the sample database? This way I can run the report and do some testing?

Arif shah

Here is the sample chart with some sample data. I want to hide nulls from the chart and also sort the chart according to month number. I want to show all months on x axis regardless of whether is a data for that month or not
 
I am not using any date field here
 
Arif

mwilliams

Okay. the reason the sorting is out of whack is that you don't have values for each series What you need to do is to create a row for each month/year combo needed to complete your time line. So, you need to make sure you have: Jan - Dec of 2013 and Jan - X of 2014 Try with this xlsx.

CaptainBob

I have the same problem with a x-axis that is a category axis, and a y-series value and a second y-grouping column. There are many different values in the y-grouping column that vary over time.
To generate a y-grouping record for every x-axis point becomes complicated, often requiring multiple queries agains the same data table.
 
It would be very useful if BIRT could do the y-split-by-grouping and then apply the sorting. BIRT appears to sort in a different sequence.
 
I am using BIRT 4.6.3