When using a date as a row in a chart, it is necessary to form groups for each date value. Otherwise, a row will be created for each date. This excess of rows will render the chart useless:
The data behind the chart looks like the following:
Instead of listing each date as a unique value, the dates should be placed into buckets in order to provide a clearer view of the data. For example, let’s group the dates by quarter. Add the “quarter” keyword to calculate the quarter of each date.
The chart is now easier to read.
The data for the chart looks as follows:
However, notice that we have lost information about the year. To solve this problem, we need to group the dates by year first, and then by quarter.
The chart now displays quarters per year.
The data is shown here:
However, what happens when the data is filtered to produce a different subset of data? The chart may become useless again.
The single row is displayed below.
In order to solve this problem, an automatic grouping function can be used. By specifying a group of “date”, the server will make several attempts with various groups. The chart with a number of rows closest to 25 will be selected and displayed to the user.
The function has decided to use year/month/week as the most optimal solution for the currently applied filter.
The data can be seen below.
When the filter is cleared and all data from the Orders table is displayed, the group mode will try to maintain the number of rows. The function has elected on using year/month.
The new data can be seen here:
A full list of available groups is displayed below.
|date||An automatic function that will attempt several combinations of groups and select the result that has a number of rows closest to 25.|
|timeofday||Takes the hours, minutes, and second of the date.||12:30:45|
|minute||Takes the minute from the date.||30|
|halfhour||All dates with minutes less than 30 will be grouped into the hour group. All dates above 30 will be grouped into the half-hour group.||1:30|
|hour||The hour will be used as the value.||1:00|
|day||Uses the day of the month.||31|
|dayofweek||Uses the day of the week. The value will be formatted according to the current culture.||Saturday|
|dayofyear||The day of the year.||156|
|weekofmonth||The week of the month. The value is derived from the first letter of the word “Week” in the current culture’s language, plus the number.||W3|
|week||The week of the year.||W16|
|month||The month of the year. The values will be formatted according to the current culture.||January|
|quarter||The quarter in the year. The value is derived from the first letter of the word “Quarter” in the current culture’s language, plus the number.||Q1|