I wish to create a line chart to display cumulative sales (running total) amounts at monthly intervals for the current year to date. I have a SalesSummary field and an InvoiceMonthNumber field that work nicely in a bar graph showing month by month sales totals, but now I wish to display how those monthly totals add up cumulatively in a line chart. Months are to be laid out on the X axis and sales on the Y axis.
If the sales are generally equal each month, the line will be relatively straight, rising at perhaps a 45 degree angle, but if monthly sales increase steadily through the year, the line will be closer to an exponential curve. Ultimately, I wish to display the current year and the previous 3 years for comparison.
Is there a simple way to create a calculation field that will total only January's sales if the month is Jan, but Jan and Feb sales together if the month is Feb, etc.?
I am happy to use ExecuteSQL if that is the best approach, but I am a beginner with SQL. Any help would be greatly appreciated.