Summarizing Data Based on Fiscal Year

The sum feature in kintone does not provide "fiscal year" as a unit of summarization. However, you can summarize data per fiscal year by creating a new field to display "fiscal year" and configuring it as "Group by" for summarization.
The following section explains the idea and a setting example of the fiscal-year-based summarization.

Example

The following shows an example of the setting. In this example, the fiscal year starts in April.

Preparations

Place the following fields on the form.

  • Date field
  • Calculated field (x 2)
  • Text field

Settings of Each Field

Open the setting screen of each field placed on the form, and configure the field name, the field code, the formula, and more as shown below.

  • Date field
    Field name: Date
    Field code: Date

  • Calculated field (the first one)
    Field name: Year
    Field code: Year
    Formula: DATE_FORMAT(Date, "YYYY", "Asia/Tokyo")
    Display format: Select "Number (1000)"

  • Calculated field (the second one)
    Field name: Month
    Field code: Month
    Formula: DATE_FORMAT(Date, "MM", "Asia/Tokyo")
    Display format: Select "Number (1000)"

  • Text field
    Field name: Fiscal Year
    Field code: Fiscal_Year
    Formula: Select "Calculate automatically" to display the input field.

IF( Date = "" , "" , IF ( Month <= 3 , ( Year - 1 ) & "Fiscal_Year" , DATE_FORMAT(Date, "Fiscal Year YYYY", "Asia/Tokyo") ) )


### Graph Setting{#calculation_yearly_summarize_1030}

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: Column chart, Clustered  
* Group by (Level 1): Fiscal Year  
* Function: Count (Number of Records)  
* Filter: All records  
* Sort by: Level 1, Ascending  



## Basic Concept{#calculation_yearly_summarize_20}

For the calculated fields, we configured the first one to display only year and the second one to display only month based on the date entered in the Date field. As the fiscal year starts in April in this example, January, February, and March belong to a different fiscal year. Therefore, the Fiscal Year field shows the previous year (current year -1) for January through March, and the current year for the rest of the months based on the value in the Date field.  

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