4 Replies Latest reply on Jun 8, 2016 6:23 AM by George Cherian

    Keeping denominator of a metric constant

    George Cherian
    George Cherian Jun 6, 2016 2:00 AM

      Hello,

      I am currently working on a tableau tool in which I need to keep the denominator constant with respect to a certain set of Filters but the numerator has to change with respect to all of the filters being used and as such am unable to do so and require help to solve this problem, below is a more in depth description of the problem.

       

      I have attached a .twbx file containing 2 worksheets with the necessary filters and data present. the first worksheet holds all the data needed and the second worksheet shows how the data should be displayed in the end.

       

      1) The total estimated hours should change based on Time Period, Utilization Type, Capability Group, Location, Level, Space Name and Space Lead

       

      2) The # of Employees should change ONLY on Capability Group, Location, Level, Space Name and Space Lead, and stay CONSTANT when Time Period and Utilization Type change.

       

      3) The # of hours per week should change based only on Time Period selected.

       

      The metric Overall Utilization uses the below calculation:

      "

      SUM([Total Estimated Hours])  // Number 1

      /

      (SUM({ FIXED [Capability Group], [Location], [Level],[Space Lead], [Space Name]:COUNTD([Employee ID])}) // Number 2

      *

      SUM({ FIXED [Week Start Date]:MAX([Std. Hours (Weekly)])})) // Number 3

      "

       

      Drop a comment in case there is any clarification needed. Any ideas on how to solve the same would be appreciated.

       

      Thanks

      • 405 Views
      • Categories: Actions & Filters, Calculations
      • Tags:
        • 1. Re: Keeping denominator of a metric constant
          Vasil Petkov
          Vasil Petkov Jun 8, 2016 4:36 AM (in response to George Cherian)

          Hi George,

           

          It would be useful if you can give an example of the correct numbers in one or two scenarios since the result you are after is hard to grasp when there's nothing to compare results to. In general you can use context filters to filter data from a FIXED expression, so making every filter but Time Period and Utilization Type a context will ensure that the context filters changes affect the FIXED but the normal ones do not. Be careful when summing countd values for you can double count people in different sections. For 3) I am not certain what you are after, should the hours change based on the Time Scale toggle ? I've given it a try based on a few minutes of looking at your data but I can't be sure it's what you are after.

           

          Let me know if I am anywhere close.

          -V

          • 2. Re: Keeping denominator of a metric constant
            George Cherian
            George Cherian Jun 8, 2016 5:06 AM (in response to Vasil Petkov)

            Thanks Vasil for the try, but this does not seem to be fixing the problem I'm having. The Utilization type filter still affects my # of Employees even if I add the rest of them as context filters.

            Apologies for not sharing a concrete example of my expected output,

            Data.PNG

            Above is my list when Utilization type is not filtered, the numbers here are all as they should be. i.e. The # of employees for Level 1 across all the months is 94

             

            Data1.PNG

            The second list shows the number of employees when we remove one particular utilization type. The value of #of employees for Level 1 SHOULD be the same as the first image i.e. 94 across all months but it has reduced and is even different for each month

             

            The Total Estimated Hours and The Weekly Standard hours - the second numeric column -  are being brought properly and don't really need any change. But the # of employees needs to be fixed irrespective of changing Utilization types.

             

            Does my explanation cover the basis of the problem?

             

            -George

            • 3. Re: Keeping denominator of a metric constant
              pooja.gandhi
              pooja.gandhi Jun 8, 2016 5:51 AM (in response to George Cherian)

              Wouldn't you just change the formula for # of employees to this? When type 3 is unchecked the numbers remain the same.

               

              • 4. Re: Keeping denominator of a metric constant
                George Cherian
                George Cherian Jun 8, 2016 6:23 AM (in response to pooja.gandhi)

                Another issue that comes up is that I need the value to change based on changes to the Level, Location, Space name ,Space Lead and Capability Group filters but stay constant ONLY with respect to the utilization type filter. With this solution I will have a fixed value when I change the filters which would be a problem. Any workaround to that?