Selection and month views

If you set up a simple month view chart such as a chart of the sum of transaction amounts by day, for example:

• using a dimension such as Day(TransDate)
• using an expression like Sum(TransAmount)

Now, if the user views this chart with no year and month selected, they will get the sum of amounts for that day of the month for all months loaded into the model. Not very useful.

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:

1. Create TransYear and TransMonth fields on loading
2. Put listboxes with these two fields on the sheet
3. Build the chart
4. Filter the chart expression(s) with the following date filter (added filter highlighted):

Sum(If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), TransAmount)


5. 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).

If you select a month only, then Max(TransDate) will be the month in the most recent year that contains data for that month. For example, in April: selecting Jan, Feb, Mar or Apr will display data for that month in the current year. Selecting any other month would display data for that month in the previous year.

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?