how to disable optional Y series grouping for secondary Y-Axis?

elvis_tu

Hello,

I want to show order annual sales volume and annual average price.

my data set:

[SQL]
select
year(orderdate) as orderyear,
month(orderdate) as ordermonth,
avg(d.priceeach) as avgprice,
sum(quantityordered) as volumn
from orders o, orderdetails d
where o.ordernumber = d.ordernumber
group by
year(orderdate),
month(orderdate)

[Preview]

ORDERYEAR ORDERMONTH AVGPRICE VOLUMN
2003 1 91.40461538461537 1357
2003 2 90.24878048780488 1449
2003 3 90.7974 1755
2003 4 91.09862068965516 1993
2003 5 90.71551724137935 2017
2003 6 88.53148936170214 1685
2003 7 92.29031746031748 2145
2003 8 88.35103448275864 1974
2003 9 94.77986842105264 2510
2003 10 90.06871951219513 5731
2003 11 90.90771428571423 10862
2003 12 91.89951807228914 2961
2004 1 90.65769230769227 3245
2004 2 91.67147727272724 3117
2004 3 91.66076923076923 2332

my chart:

[Design]

I design a stacked bar chart for monthly sales volume with
secondary axis which series type is "Line Series":

category (X) series: row["ORDERYEAR"]
optional Y series grouping: row["ORDERYEAR"] + "-" + row["ORDERMONTH"]
Y axis 1: row["VOLUMN"]
Y axis 2: row["AVGPRICE"]

[Preview]
Stacked bar chart for monthly sales volume is OK.
But line chart also draw average price monthly.

How could I let line chart draw annual average price?
(i.e. disable optional Y series grouping for Y axis 2 )

Thanks,

bhanley

I think the main issue here is that you have valid values in the data set for each of the grouped points. In other words you do not have an annual average in your data set, you have a monthly average which maps into the grouped strategy you are applying to the volume data. I think the chart is doing exactly what it should do in this case.

I built a sample out that has the two series rendered on their own chart and then in combined form (as you want it to). I think you will see what i am talking about when you look at the stand-alone line chart. It is rendering exactly what is configured.

I think we will have to look at modifying the data set somehow to achieve what you are looking for. If you notice on the line chart in my test report, there is no Y-series grouping and asa result there is a ton of points mapped (one per month). You will need to calculate an annual sales number for the Yer before you take the data to the chart engine. The chart engine will render the data well, but it is not designed to manipulate the data before plotting it.

This did not get things working for you I understand, but I hope it helps get things straight in terms of why things are behaving like they are.