Data Aggregation on a per Fiscal Year Basis

Article Number:040761

The data aggregation feature of Kintone does not provide "fiscal year" as a unit of data aggregation. However, you can perform data aggregation on a per fiscal year basis by creating a "Fiscal Year" field and adding it to Group by in the relevant Graph settings.
This article provides an example of configuring the settings for fiscal-year-based data aggregation. In this example, the fiscal year starts in April.

Preparation

Open the Form tab in the App Settings and place the following fields on the app form.

  • (1) "Date" field
  • (2) "Calculated" field
  • (3) "Calculated" field
  • (4) "Text" field

Settings of each field

Open the settings screen of each of the fields you have placed on the app form, and set the field name, field code, formula, and display format.
Overview of Field Codes

(1) "Date" field

  • Field name: Date
  • Field code: Date

You do not need to set a formula or display format for this field.

(2) "Calculated" field

  • Field name: Year

  • Field code: Year

  • Display format: Select Number (1000).

  • Formula: Set the following formula.

    The content has been copied.
    DATE_FORMAT(Date, "YYYY", "Asia/Tokyo")

(3) "Calculated" field

  • Field name: Month

  • Field code: Month

  • Display format: Select Number (1000).

  • Formula: Set the following formula.

    The content has been copied.
    DATE_FORMAT(Date, "MM", "Asia/Tokyo")

(4) "Text" field

  • Field name: Fiscal Year

  • Field code: Fiscal_Year

  • Formula: Select Calculate automatically and set the following formula.

    The content has been copied.
    IF( Date = "" , "" ,
    IF ( Month <= 3 , ( Year - 1 ) & "Fiscal_Year" , DATE_FORMAT(Date, "Fiscal Year YYYY", "Asia/Tokyo")
     )
    )

You do not need to set a display format for this field.

Graph settings

Open the "Create Graph" screen and configure it as follows.
This example creates a column chart with the fiscal year on the horizontal axis and the number of records on the vertical axis.

  • Chart Type: Select Column chart and Clustered.
  • Group by (Level 1): Select Fiscal Year.
  • Function: Select Count.
  • Filter: Select All records.
  • Sort by: Select Level 1 and Ascending.

Formula explanation

From the date entered in the "Date" field, one "Calculated" field is configured to display only the year, and the other is configured to display only the month. Since the fiscal year starts in April in the example above, the calendar year and fiscal year do not match for January, February, and March. For this reason, the "Fiscal Year" field displays January through March with the value (Year - 1), and the rest of the months with the year as it is entered in the "Date" field.

The DATE_FORMAT function of Kintone handles its calculation result as a string. However, if the result of the DATE_FORMAT function in the "Calculated" field consists only of a numeric value, it can be handled as a numeric value.