IIUC, your issue is that you have two y-axis series, and they overlap – though I'd expect to see the 2015 figures positioned above the 2014's months, not vice versa …
See if the attached file does what you need; it calculates the month count of the previous year to create placeholder positions that shift the start of this year's y-series to the correct position on the x-axis (corresponding to its first month).
(I realize that this could (probably) be done directly in SQL, using code along the lines of
WHEN year(dateOrder) = Get ( CurrentDate ) - 1
THEN ' ' + CHAR(13)
ELSE SUM (amountTotal)
WHERE Year(dateOrder) IN (" Year ( Get ( CurrentDate ) ) & "," & Year ( Get ( CurrentDate ) ) - 1 & ")
ORDER BY …
GROUP BY … "
but couldn't get CASE and SUM() to play nicely.
EDIT: On re-reading, the problem in your current calculations actually seems to be that you're sorting by the numerical cMonthNumber; so this series of matching distinct month numbers
will be sorted as
Solution would be to simply use the date field to ORDER BY in all your calculations.
ChartBySQL_eos.fmp12.zip 67.9 K
Thanks erolst. I tried changing the order by to the OrderDate and 9,10,11,12 came before 1,2,3,4 but all the amounts were in the first section. I thought it might help to see the chart . See below - with the data I have, all the blue should be over 9,10,11,12 and all the yellow should be 1,2,3,4 - it happens with this customer that their first sale was 9/2014. So I'd want to see zero for 2015 above 9,10,11,12 and zero for 2014 above 1,2,3,4. I'm trying to show a comparison of sales this year v last year. I don't get why the sales show in the wrong month. Thanks for the file. I think this is set up to show ongoing sales as a line chart - perhaps I could use the file to mock up my scenario and post it back.
Should my X-axis just be a delimited list 1 through 12 perhaps?
Ok, so the delimited list of month numbers may be the way to go so all show up, but my sales for last year still show up in Jan-Apr not Sep-Dec...
Ok, I think that the delimited sales data needs to have 12 values. Because there's no sales in Jan-Aug 2014, the eSQL just has something like:
And this starts out at the beginning of the chart. What I think the eSQL should return is:
0 ... and 6 more zeros to represent all months up to Sep, and then
So, new question! How would I write an eSQL to evaluate sales for each month and return zero if there was nothing found for a particular month?
The only correlation between the x-axis and the values of the y-series is their list position; this means you need (as you have found out for yourself) to create placeholder values to properly align the y-values with the x-labels, either empty or zero.
Try the attached file.
When comparing to last year, usually there are at least longer than 1 year of data, so do you need calculating X-axis?
If use all 12 months in X, I did it a month ago in SQL: way to return 0 instead of null?
This can be modified for less than 12 months, but?
usually there are at least longer than 1 year of data, so do you need calculating X-axis?
If I interpret the attached screenshot correctly, this is intended as multiple data series to compare over 12 months, so the x-axis can be a simple months list; the challenge is to include the list position for months with a null total, which can be done using a utility table (absent of being able to define an SQL variable as virtual pointer) to LEFT OUTER JOIN into the values table.
PS: Thanks for the link; interesting discussion which I missed completely!
Thanks again erolst for taking the time to provide a sample file - had you said "create a utility table for months and do a left outer join to it", I'd have been stumped again! I did get this implemented late last night and it seems to work well. Problem solved, learned something new - the best of outcomes. Much appreciated.