Calculated fields: Groupings

At some point you'll want to display a chart with data grouped by day, month, quarter or year. For example, here's the number of invoices grouped by Month:

Data grouped by month.

The short version of this is: create a calculated field which calculates the label (the year and month, in the example above).

Then in your web part configuration, choose your calculated column as the "grouping" field:

And for the sort order, choose to sort by categories:

Note that the categories - in this case, a calculated date label - are automatically recognised as dates, and will be sorted chronologically and not alphabetically.

If you'd like to group your data by Financial Quarter, you'll need two things:

• a Date field which is already populated with the correct date for each row

• a Calculated field which calculates the Quarter (or maybe month, year, etc).

The formula for to produce a Quarter calculated field, where Q1 is January-March, is as follows:

=YEAR(Created)&" Q"&CHOOSE(MONTH(Created),1,1,1,2,2,2,3,3,3,4,4,4)

The formula uses Created as the date field - change this if you have another date field.

It's easy to customise - e.g. if your Q1 starts in April, try this instead;

=YEAR(Created)&" Q"&CHOOSE(MONTH(Created),4,4,4,1,1,1,2,2,2,3,3,3)

Simply create a calculated field and paste the formula in:

Note that the output type of this calculated field must be Text. Grouping/Aggregation does not work for other types.

The output in your SharePoint list will be something like this:

The reason the Year component is first is so that the field is easily sortable. You can then use this to display a Quarterly sales chart, for example.