📊
ListBurst Charts
  • Introduction
  • Getting started
    • Installation (AppSource)
    • Installation (Manual)
    • Add the web part to a page
    • Configure a chart
    • Filtering
  • Advanced chart types
    • S-curve
  • Teams
    • Adding to Microsoft Teams
    • Customise a Team chart
    • Add an existing dashboard
  • SharePoint Tips
    • The List View Threshold
    • Calculated fields: Groupings
    • Calculated & Multi Valued field aggregations
    • Find your App Catalog
  • Releases
    • Changelog
    • Upgrade process
    • Security & Architecture
Powered by GitBook
On this page

Was this helpful?

  1. SharePoint Tips

Calculated fields: Groupings

PreviousThe List View ThresholdNextCalculated & Multi Valued field aggregations

Last updated 28 days ago

Was this helpful?

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:

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.

Data grouped by month.