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.