After some exploring, figured out a workaround. Before I explain the work around, here is background and problem statement.
Background: If you follow the documentation and create a dynamic chart. You would have create a schema as per documentation example of ‘Values’ holding x,y and this has a many to 1 relationship link to ‘Series’ which will then hold, series name, line colour and fill colour.
Problem statement: You have a requirement to show a green bar for certain values in the chart and a red bar for certain values. Lets say, your X axis is months and Y axis is attendance percentage. If you have more than 80% for a month, you want to show it as green, if its less than 80%, you want to show it as red. You also want the chart data in chronological order, meaning Jan to Dec (or) Dec to Jan. In this scenario, what you will observe when you populate data is that all green bars and red bars are grouped and then the sorting on X-Axis works within this group. So, if a sample data has more than 80% attendance for Jan to March and June to Dec, you will see all these months first and then you will see 2 red bars for April and May at the end.
Potential Root Cause: So, here is what i think is happening. The graph is trying to collate the data by using a query to join values and series tables. It takes into account the sorting config we provided for values and for series. Though we dont configure any sorting for series, the underlying code is written in a way that resulting list is grouped by series and then are sorted within the group by values.
Work around: This is a work around and not a solution. Since we now believe this is how the code underneath is working, we can put the series data in values table and make a self reference. This will make each plot as a single series, this means multiple records cannot be clubbed together. Limitation: if you enable legends, you will see as many legend entries as number of bars plotted. so i recommend turning off legends and provide narrative somewhere in your page. output is shown below.
New Data Model:
Output with legends on:
Output with legends off: