Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help with Matrix

I want to add a matrix to my report. I need it to look like the image below.

 

I have 4 tables:

  • Employee info table
  • Employee Vacation Days balance table
  • Vacation type table (has two values: "Planned Vacation" and "Actual Days Taken")
  • Planned Vacation table whose columns are the employee ID, each individual month (JAN, FEB, MAR, etc...), and Year (2020 for now) and each row has distinct employee, the number of days they are planning to takeoff of the corresponding month column.
  • Actual Days Taken table whose columns are the employee ID, each individual month (JAN, FEB, MAR, etc...), and Year (2020 for now) and each row has distinct employee, the number of days they are actually taking off of the corresponding month column.

How do I arrange the data in the Rows, Columns, and Values fields to get the result in the image?

Need Help

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    Anonymous 

     

    From your desciption, you should:

    1) unpivot both "Planned Vacation" and "Actual days taken" tables to have the months and Year in a single column each.

    2) Create lookup tables with unique values for:

                a) months (include an index column for sorting purposes):  join with a one-to.many relationship to both your "Planned     Vacation" and "Actual days taken tables"

                b) years:  join with a one-to.many relationship to both your "Planned Vacation" and "Actual days taken tables"

               c) Employee ID: join with a one-to.many relationship to all your tables

    3) create measures to calculate the number of [planned days],  [number of days taken] and [vacation days balance]

    4) create a matrix visual using:

                a) Employee Id field from your lookup table as rows

                b) the month and year fields from your lookup tables as columns (or you can use the year field as a filter in the filter pane if             you want to restrict the data to a specific year.)

                c) include your measures as values

    5) go into the formatting pane for the matrix and under "Values" select "show on rows".

    Does that work?

    If not please provide sample data in data format for all tables to replicate your model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      PaulDBrown 

      Thank you for your reply. I got it to work except for the circled column.

      Here's how I arranged everything:

      Rows: EmployeeInfo

      Columns: Months (Single column)

      Values: Planned, Actual

      I have a table named Balance with the two columns: EmployeeID and LeaveBalance. I connected it to the Employee table this way:

      EmployeeID in Employee table (1)-->(*) EmployeeID in Balance table

      How do I get the Leave Balance to display as I want it in the image above?