So I have a data table which displays the amount of money saved up per date, grouped by year and month, and aggregated by amount saved (portfolio purchases).
I am trying to generate an area chart which displays the historical amount of money saved, but aggregated towards the previous purchases (ie month1 = month 1, month2 = month1+month2, month3 = month1+month2+month3, etc..).
Currently if I aggregate the data from the sum of the amount saved, I get the total saved per month, which is nice, but I'd like to see the information in aggregate form to understand how much I've saved in total over time.
Any help would be much appreciated.
Thanks
Hi Diego,
there are different solutions using the OVER keyword or a self-join. You can find a handy description here:
https://www.complexsql.com/how-to-get-cumulative-sum-in-sql-using-analytical-function/
You can try to configure the self-join in your aggregate but it might be easier to use a SQL query.
Hi Sebastian this has worked well up to a point, however I'm getting an error while trying to group by month and year.
1. With your recommendation I managed to calculate cumulative sum and also extracted Year and Month from Date for grouping purposes.
2. However when trying to group by Year and Month I get the following error.
Any ideas on how to proceed?
For better readability I would suggest splitting it up into two steps:
WITH MONTHLYSUM_CTE AS ( SELECT DATEPART(YEAR, {Purchase}.[DatePurchased]) AS Year, DATEPART(MONTH, {Purchase}.[DatePurchased]) AS Month, SUM({Purchase}.[AmountPurchased]) AS AmountSum FROM {Purchase} GROUP BY DATEPART(YEAR, {Purchase}.[DatePurchased]), DATEPART(MONTH, {Purchase}.[DatePurchased]))
SELECT Year, Month, AmountSum, SUM(AmountSum) OVER (ORDER BY Year, Month) AS CumulatedSumFROM MONTHLYSUM_CTE
My suggestion is to do an OnAfterFetch action that will do those calculations.
Thanks! Would you have any links or screenshots for reference?