3 Replies Latest reply on Jan 23, 2018 1:44 PM by Manuel Velasquez

    Aggregating two different averages per each month

    manuel.nellen

      Hello community

       

      I have the following challenge:

      I'm trying hard to vizualise two bars that represent the average capacity on the top 5 days and another with the remaining days (without weekends).

       

      Data Source

      Each row is for a date and the columns consist of productive data and staff availability.

      [Total all regions] is the total workload and [Total Staff] is the available staff. [Capacity] is simply the productivity divided through available staff which reults in the teams capacity for that particular day.

       

      In Sheet 1 the scope is to compare capacity with the total of all regions. At the moment in this draft there is only the general capacity for the whole month.

       

      Now this is where I want to get:

      I want twin bars one showing the average capacity of the top 5 days as far as [Total all regions] is concerned. And another bar that shows the average capacity for the remaining days of the month (exluding the weekends which always will have a null value).

       

      Any suggestions how to solve this in a pragmatic way?

       

      I tried through ranking (RANK) the date by volumes each month, but didn't get far.

       

      Example attached.

        • 1. Re: Aggregating two different averages per each month
          Manuel Velasquez

          Hi Manuel

           

          Let me show you one approach that you could use, this approach requires some data preparation ( Pivoting the data), Creating Sets, and some calculations, I created a copy of the file you provided and clean up some calculations, except for the total staff calc.

           

          Pivoting the data.

          To get the productivity throughout all regions I will pivot those 4 columns

           

          1.png

           

          I'll rename the resulting columns as Pivot Field Names="Regions" and  Pivot Field Values="Productivity"

           

           

           

          2.PNG

           

          Creating the calculations

           

          Capacity ( As you defined)

          3.PNG

           

          AVG Capacity

           

          4.PNG

           

          NOTE: on Average calculations "NULL" are ignored

           

           

          Creating the Sets:

          So for your request of showing the avg capacity for the Top 5 days by productivity and the rest of the month, I will create a set based on Datum dimension to pick those top 5 days

          5.png

           

           

           

          6.PNG

           

          Double checking the Set result ( In - Out).

          Elements IN  means = IN the top 5 per productivity

          Elements OUT means = OUT of the top 5 per productivity

          You can change the aliases to make it user-friendly

           

          6a.PNG

           

          Showing the AVG Capacity calculation for the Top 5 days and rest of the month 

           

          8.PNG

           

           

          Let me know if this help!

           

          Attached .twbx file for reference.

           

          Manuel Velasquez

          1 of 1 people found this helpful
          • 2. Re: Aggregating two different averages per each month
            manuel.nellen

            Hello Manuel

             

            Thank you for your input, very much appreciated.

             

            The following problems appear:

             

            • Pivoting the data muliplies the availalbe staff by region resulting in a lower capacity by factor 4. This would need minor adjustments in some formulas to solve it. I would pivot the data only if this is unavoidable and adds added value. Reason: I have other work sheets on this data which work already well. A pivot would create issues with them. The capacity does not have any relation to the workload in the regions as staff works always simultaneously in all regions. Therefore please ignore the regions, they don't have any impact on the of the capacity calculation.

             

            • The Set does not consider each month separately. Apologies if I didn't point this out more clearly in my initial post.

                The final result should be something like this:

             

                One bar representing capacity on the top 5 days and the other bar representing the other days.

            example.jpg

            I added the worksheet and in Sheet1 there is my draft. The side by side bars look already fine, but the Top5 days are on a yearly not on a mothly level. If this can be solved then I am fine.

             

            Having the line graph of [Total all regions] in the same sheet would be nice to have. Otherwise I could combine the two in a dashboard.

             

            graphs.jpg

            • 3. Re: Aggregating two different averages per each month
              Manuel Velasquez

              Hi Manuel

               

              Ok, Ranking per month, in Tableau when using Tableau Calculations (Rank in this case) you need that level of detail on the view (in this case every day), and as I understand you want to present the data at a monthly level.

               

              So, if we are not able to re-shape the data, the other option could be doing the RANKING PER MONTH at the data source level, and keeping the same structure.

               

              In this other approach, I'm using SQL Server and a Custom Query to create the ranking per month.

              This is the custom query to create the ranking per month ( I also create TotalAllRegions and TotalStaff at this level also, but you can remove from the query if you already have it on Tableau)

               

              1a.PNG

               

              Result, notice that the ranking reset every month

              2a.PNG

               

              Creating the Set

              Now that we have this ranking at the row level, create the set for the top 5 per month.

              NOTE: We need to convert "RankInMonth" field to a dimension before doing the set as Tableau see it as a measure.

               

              3a.PNG

               

               

               

              NOTE: Capacity and AverageCapacity calculations are the same as you defined

              Capacity= [TotalAllRegions]/[TotalStaff]

              AverageCapacity = SUM([Capacity])/COUNT([Capacity])

               

              The view

              4a.PNG

               

              Attached .twbx for reference

               

              Let me know if this helps!

               

              Manuel Velasquez