- using a dimension such as Day(TransDate)
- using an expression like Sum(TransAmount)
Another approach is to make the chart display summed amounts for the current month. If a selection is made, then display summed amounts for the selected month/year.
This is how I do this:
- Create TransYear and TransMonth fields on loading
- Put listboxes with these two fields on the sheet
- Build the chart
- Filter the chart expression(s) with the following date filter (added filter highlighted):
- Now, if nothing is selected, the TransDate range will be in the current month (or at least the last month in which a transaction was recorded - in most cases this will be the same date).
Sum(If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), TransAmount)
If you select a year only, then Max(TransDate) will be the last day of that year with transactions. If that is the current year, then that will be today (or yesterday).
Any other selection (of TransDate, TransMonth, TransYear) will cause the month with of the last possible TransDate to be displayed.
You can, of course, do the filtering with a set expression if you prefer. I find this syntax more intuitive, but the final choice should depend on performance. Maybe you would like to convert this to a set expression in a comment below?