Help with a SUM in a chart please

RLJB

I'm trying to make a presentation with a Line chart.

I have a BO called Invoices with two attributes Date and Amount.

Date is a date attribute. Amount is a number attribute.

I would like the x Axis to have Jan, Feb, Mar, Apr,...etc to ... Dec.

And the Y axis to have a SUM of the total Invoices.Amount for each month ie what was the total value of invoices for each month.

Then I would like to have each line be a Year with a different colour eg 2009 is a blue line and 2010 is a green line. So they can compare total invoice amounts in Jan for this year versus last year.

I have a query getting all invoices and a display result being a custom presentation.

In the Presentation Chart in the X Axis I have:
MONTH (Invoices.Date)

and in the Y Axis I have:

SUM Invoices.Amount WHERE (MONTH (Invoices.Date) = MONTH (Invoices.Date))

This isn't working, it is incorrectly:

doing a cumulative total for invoices for each month
not showing each year separately (I think I need a group but I'm not sure)

Can anyone provide some tips? I have looked at the report samples bsv but they aren't doing sums and groupings like I am.

Thanks

Rod.

aware_support

Different years should be represented as different "series" on the chart.

Your SUM expression is wrong, but I can't think of the right one right now - maybe it's an omission from the product (there should be an option to specify the value of the X-Axis in the expression:

SUM Invoices.Amount WHERE MONTH (Invoices.Date) = { X-Axis Value }

I can't remember if we have this option...

RLJB

Hi Support

Thanks for your help, I have played around with it some more and I'm getting closer, the totals now sum correctly, these are the settings and output as shown below (I have a bar and a line version FYI):

The settings in the x and Y axis are as follows:

The data series is as follows:

A few problems that I don't think I can overcome:

a) the line chart from the data series is hidden behind the bars from the bar chart, is there anyway to bring it forward so you can see it?

b) because the 2009 figures are on a data series with a different axis, the scale is different to the previous years, so you can't actually compare the two years figures (which is the point of exercise). Can I get them on the same axis? (if I just add two data series then the graph breaks and doesn't display at all, you must have something in the Y axis expression)

c) when you put in data labels for the values it actually provides a label not just for the totals but for every amount that makes up the total, making it really hard to read. It also does this for the draw markers (which is why you can see 5 red dots in the Feb year 2010 because there are five amounts in Feb).... is there any way to just have the total show, not all the individual amounts?

Thanks

Rod.