Custom Reports
      Back to home
      1. Knowledge Base
      2. Reporting
      3. Custom Reports

      Fiscal Year Reporting: Services or Involvement Duration

      This article will cover how to use Calculated Fields to create a report that shows clients with a case involvement or enrolled in a service during a specified time period.

      How to create a report that answers the question: How many clients received a service during each quarter (or month)? 

      1. Create a New Visualization 
      2. Select the Case_Involvements Dataset 
      3. Change the Chart Type to Grid Screen Shot 2022-04-01 at 12.46.10 PM
      4. Drag and drop the following fields into the Grid Data section under Columns
        1. Involvement type
        2. Involvement Start Date
        3. Involvement End Date
      5. Add a Filter for Involvement_Type and select the involvements that you wish to track in this report
        Screen Shot 2022-04-01 at 12.50.16 PM
      6. Add a new Calculated Field by selecting the + Icon in the Fields List, then selecting Calculated Field
        Screen Shot 2022-04-01 at 12.53.20 PM
      7. Name the Field, then add the following formulas: Please note that you will have to repeat this process for each formula
        1. FY 2022
          1. if(and([start_date_involvement]> date(2021,12,31), [start_date_involvement] < date(2023,1,1)),"Fiscal 2022",if(isempty([end_date_involvement]), if(and([end_date_involvement] < date(2023,1,1), today()> date(2021,12,31)), "Fiscal 2022", "Not Fiscal 2022"),if(and([start_date_involvement] < date(2023,1,1), [end_date_involvement]> date(2021,12,31)), "Fiscal 2022", "Not Fiscal 2022")))
        2.  Q1 FY22
          1. if(isempty([end_date_involvement]),if(and([start_date_involvement] < date(2022,4,1),(today() > date(2021,12,31))),"Fiscal Q1","Not Fiscal Q1"),if(and([start_date_involvement] < date(2022,4,1),([end_date_involvement] > date(2021,12,31))),"Fiscal Q1","Not Fiscal Q1"))
        3. Q2 FY22
          1. if(isempty([end_date_involvement]),if(and([start_date_involvement] < date(2022,7,1),(today() > date(2022,3,31))),"Fiscal Q2","Not Fiscal Q2"),if(and([start_date_involvement] < date(2022,7,1),([end_date_involvement] > date(2022,3,31))),"Fiscal Q2","Not Fiscal Q2"))
        4. Q3 FY22
          1. if(isempty([end_date_involvement]),if(and([start_date_involvement] < date(2022,10,1),(today() > date(2022,6,30))),"Fiscal Q3","Not Fiscal Q3"),if(and([start_date_involvement] < date(2022,10,1),([end_date_involvement] > date(2022,6,30))),"Fiscal Q3","Not Fiscal Q3"))
        5. Q4 FY22
          1. if(isempty([service_end_date]),if(and([service_start_date] < date(2023,1,1),(today() > date(2022,9,30))),"Fiscal Q4","Not Fiscal Q4"),if(and([service_start_date] < date(2023,1,1),([service_end_date] > date(2022,9,30))),"Fiscal Q4","Not Fiscal Q4"))Screen Shot 2022-04-01 at 1.13.23 PM
      8. Drag these new fields into the Grid Data section under ColumnsScreen Shot 2022-04-01 at 1.14.41 PM
      9. Save the report by selecting the checkmark icon in the upper right-hand cornerScreen Shot 2022-04-01 at 1.21.10 PM
      10. If you would like to sum the rows, you will need to export this report to Exccel
        1. At the bottom of the last row under the columns D-H use the formula below
          to sum the number of residents per time range.
          1. For Column D:      =COUNTIF(range, "Fiscal 2022")
          2. For Column E:      =COUNTIF(range, "Fiscal Q1")
          3. For Column F:      =COUNTIF(range, "Fiscal Q2")
          4. For Column G:      =COUNTIF(range, "Fiscal Q3")
          5. For Column H:      =COUNTIF(range, "Fiscal Q4")
      11. For Column E:      =COUNTIF(range, "Fiscal Q1")

      Screen Shot 2022-04-01 at 1.31.10 PM