Hi,
I managed to do last year vs. this year (or last month vs this month) comparisons, maybe the SQL code will work for you too:
Note: this code is for average donation per quarter (the last 4 quarters) vs the same quarters last year.
This is how it will look like as table: (when you want to have a graph just remove the two middle columns and you can create one - take care to revome the concat function before to get the real numbers without the "%"
Durchschnittsspende = Average Donation
Wachstum zum Vorjahr = Growth compared to the year before
Spendenquartal = Donation quarter (date)
And that is the code (I will translate some of the fields to english to make clear what I mean - otherwise just ask :))
SELECT Spendenquartal, t2017.sum AS Durchschnittspende_2017, t2016.sum AS Durchschnittspende_2016, concat(round((t2017.sum-t2016.sum)/t2016.sum*100), "%") AS Wachstum_zum_Vorjahr
FROM
(
SELECT sum(donation_sum)/count(donation_id) AS sum, quarter(date) AS quarter1, concat(date_year, " - Q", quarter(date)) AS Spendenquartal
FROM donation_table
WHERE str_to_date(concat(date_format(donation_table.date, '%Y-%m'), '-01'), '%Y-%m-%d') BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -4 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(date_add(now(), INTERVAL -1 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY quarter1
Order by date_year asc, quarter(date) asc
) AS t2017
LEFT JOIN
(
SELECT sum(donation_sum)/count(donation_id) AS sum, quarter(date) AS quarter2
FROM donation_table
WHERE str_to_date(concat(date_format(donation_table.date, '%Y-%m'), '-01'), '%Y-%m-%d') BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -8 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(date_add(now(), INTERVAL -5 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY quarter2
Order by date_year asc, quarter(date) asc
) AS t2016
ON t2017.quarter1 = t2016.quarter2