3 Replies Latest reply on Nov 6, 2018 6:43 AM by Brandon Kidwell

    Table and other complex calculations

    alex alex

      Hi all!

       

      I am stuck with some aggregations here...

       

      I have a source data to calculate employee utilization (actual time spend on projects divided by total standard working hours for the period). Then I compare actual utilization rate with KPI and decide wether the employee is under/over-utilized etc.

      Also, employee might change the Grade for the period - in that case we need to use his/her last (highest) grade to calculate utilization but use all actual hours (during both new and old grade) for calculation.

       

      As a result I am getting a table like this:

      11.JPG

      Here we can see that for example Employee5 with Grade G1 in Department1 is underutilized with actual utilization rate of 55%.

       

       

      Now I need to:

      1. Create a table with a summary per grades/utilization

      It should look like:

      12.JPG

      Where we calculate the amount of employees with over/under-utilization and group it by Grades/Departments.

      In the first table we had 1 underutilized employee in Department1 - and here in table #2 we can see "1" colored blue next to Department1.

      Or in the first table we had 2 Overutilized employees in Department2 of grade G3 - and here on table #2 we can see "2" colored Orange in "G3" section.

      etc.

      And it should be colored like in table 1.

       

       

      2. Add several of this calculated numbers to a dashboard with text, like:

      "There are X over-utilized employees in Department 2"

      or

      "There are Y over-utilized employees of grade G3 in Department 2"

      In our example, X = 3 and Y = 2, but it should be calculated with a formula (or how Tableau does this ).

       

       

      Can you help me to do this?

       

      Source .twbx file attached.

       

      P.S. Currently applied filters are correct. Please un-check JunkProjects to calculate utilization.

        • 1. Re: Table and other complex calculations
          Brandon Kidwell

          Hello Alex,

           

          I think I can get you most of the way there but you might need to add a static table and join on the Utilization classifications to complete your table.

           

          You can filter out the lower grades (if more than one) using a Boolean filter:

           

          RIGHT([GRD],1) = { FIXED [EMPL]:MAX(RIGHT([GRD],1))} and filter on TRUE

          UtilTest_2.JPG

          When Employee is included in Rows the calculations seem to be correct.  When you remove Employees the categories with Null or 0 roll up to Underutilized.  Since your categories are formula based this is difficult to "force" the categories when they derive from a measure.  You might want to find a way to add a table of Utilization categories and the requirements for Standard Hours and KPI Util and join to the table.  Using the Table as the primary will keep all options available as a column and they will simply populate when the requirements are fulfilled based upon your calculations.

          UtilTest_3.JPG

           

          I hope this helps,

           

          thanks,
          Brandon

          • 2. Re: Table and other complex calculations
            alex alex

            You might want to find a way to add a table of Utilization categories and the requirements for Standard Hours and KPI Util and join to the table.  Using the Table as the primary will keep all options available as a column and they will simply populate when the requirements are fulfilled based upon your calculations.

            Sorry it is quite difficult for me to understand. Could you maybe show an example of what you suggest to do?

            I want to use Tableau to aggregate data in different perspectives and be able to manipulate the time periods / levels of aggregation to calculate Utilization (by employee, by grade, by department / by quarter, by month, by year).

            If I calculate the static data for a year and load this static table just for visualization, the flexibility will suffer.

            Just give me an example of what you think should be pre-calculated, and what could remain flexible...

            • 3. Re: Table and other complex calculations
              Brandon Kidwell

              Hello Alex,

               

              My original thought was to create a separate table with the categories you have for Utilization in a column then join on the table so you have the static columns shows as you described.  I tried this and since your Utilization formula keeps it as a measure I have not found a way to convert to a dimension and have it act in the desired way.  Another issue I was unable to resolve was keeping the Actual Time_ZERO from hiding when Employee is removed from the rows shelf.  Once you remove Employee the Actual Time_ZERO identified employees group into Underutilized (see below).  If you don't have a solution for this in Tableau I'd recommend making your Utilization formula prior to importing into Tableau (SQL or Excel) then you should have all the functionality you desire. 

               

              My apologies I couldn't get you all the way there but I hope this helped!

               

              Thanks,

              Brandon

               

              UtilTest_4.JPG