How to Refine Data

[modesu]

I am currently setting up the following database:

In the current DB and functions, the "Submission Year" information is not held in the "Employee Table" and "Improvement Proposal Table". Also, "Problem Raising pt" and "Department-wise Problem Raising pt" are not held by fiscal year.

I would like to display "Problem Raising pt" and "Department-wise Problem Raising pt" by fiscal year. Can you modify the DB configuration and functions to accommodate this? If possible, please tell me how to do it.

* I do not want to manually select the fiscal year at the time of input.
* I do not want to manually create slices for each fiscal year.

【DB】
■Table: Fiscal Year
_RowNumber
Fiscal Year ID
Fiscal Year Name

■Table: Improvement Proposal
_RowNumber
Improvement Proposal ID
Improvement Proposal Title
Problem Raiser_Employee ID＜REF:Employee＞
Improvement Planner_Employee ID＜REF:Employee＞
Improvement Implementer_Employee ID＜REF:Employee＞
Status＜REF:Status＞
Proposal Category＜REF:Proposal Category＞
Creation Date and Time
Last Update Date and Time
Submission Year＜REF:Fiscal Year, FORMULA：

IF(
MONTH([Last Update Date and Time]) >= 5,
YEAR([Last Update Date and Time]),
YEAR([Last Update Date and Time]) - 1
)

＞

・Note: The fiscal year is from May 1st to April 30th every year.

■Table: Employee
_RowNumber
Employee ID
Name
Affiliated Department＜REF:Department＞
Problem Raising pt＜FORMULA：COUNT(SELECT(Improvement Proposal[Problem Raiser_Employee ID], AND([_THISROW].[Employee ID] = [Problem Raiser_Employee ID], [Status] = "Improvement Report Completed")))＞

Related Improvement Proposals By Problem Raiser_Employee ID＜REF_ROWS("Improvement Proposal", "Problem Raiser_Employee ID")＞

Creation Date and Time
Last Update Date and Time

■Table: Proposal Category
_RowNumber
Proposal Category ID
Proposal Category Name
Related Improvement Proposals＜FORMULA：REF_ROWS("Improvement Proposal", "Proposal Category")＞

■Table: Department
_RowNumber
Department ID
Department
Department-wise Problem Raising pt＜FORMULA：SUM(SELECT(Employee[Problem Raising pt], [_THISROW].[Department ID] = [Affiliated Department]))＞

[Hieran_Del]

---

@modesu wrote:

I would like to display "Problem Raising pt" and "Department-wise Problem Raising pt" by fiscal year. Can you modify the DB configuration and functions to accommodate this? If possible, please tell me how to do it.

---

Would displaying a table for an employee with "Problem Raising pt" grouped by fiscal year be part of what you're thinking about?

You could achieve this by creating a table with the following fields:

Table "Employee Fiscal Year":
_RowNumber
Employee Id (Ref, Employee table)
Fiscal Year Id (Ref, Fiscal Year table)
Problem Raising pt (number, virtual column with app formula below)

COUNT(SELECT(Improvement Proposal[Problem Raiser_Employee ID],
 AND(
  [_THISROW].[Employee ID] = [Problem Raiser_Employee ID],
  [Status] = "Improvement Report Completed",
  [_THISROW].[Fiscal Year Id] = [Submitted Year]
 )
))

The same principle could be applied to the data from the department table. You can use "Group By" to group the data by fiscal years in an inline table (appears in a "Related Employee Fiscal" column (list of Ref) that is generated, both for the Fiscal Year table and Employee tables).

Secondly, to automate the table's Employee Id and Fiscal Year Id data, write a function in A2 in the spreadsheet, such as:

=LET(employeeIds, QUERY(Employee!A2:A, "select A where A is not null"), fiscalYearIds, QUERY('Fiscal Year'!A2:A, "select A where A is not null"), employeeCount, ROWS(employeeIds), fiscalYearCount, ROWS(fiscalYearIds), totalRows, (employeeCount * fiscalYearCount), IF(totalRows = 0, IFNA(NA()), MAKEARRAY(totalRows, 2, LAMBDA(rowIndex, colIndex, IF(colIndex = 1, INDEX(employeeIds, QUOTIENT((rowIndex - 1), fiscalYearCount) + 1, 1), INDEX(fiscalYearIds, MOD((rowIndex - 1), fiscalYearCount) + 1, 1))))))

That will fill in the first two columns with every combination of employee id and fiscal year id.