4 Replies Latest reply on Feb 9, 2012 2:49 AM by Jonathan Drummey

    Show two different type of attrition in same graph

    Gourav Kumar

      Hi all,

       

      Please help me out. I have gone through many posts but did not find a solution.

       

      First Question:

       

      I am trying to display two types of attrition in a single chart. I have attached the data.

       

      My problem is

       

      if ‘A2’ is selected in New HL1 and ‘B10’ in New HL2 then the calculation for

       

      “Normal Attrition” will only show for employees (FTE) of ‘B7’ within ‘A2’ regardless of country (Blue one) (this is what is shown here)

       

      “Geography attrition” (this is the tricky part) should be that it counts all employees every country where the country has a ‘B7’category (Orange one)

       

      The final graph will look something like in Sheet2(this one done by changing axis to simulate the view that we will have).

       

      Also we are displaying annual attrition so, for Attrition we need to count for all quarters but FTE only for last quarter. i.e.

       

      Sum(attrition)/Sum(Q4FTE)

       

      Kindly let me know if I am not very clear. I have been banging my head over it for quite some time, using parameters, making columns for totals of country etc. but not able to do it.

       

      Second Question:


      Is it possible to display all columns for New HL1 when ‘(All)’ is selected and then when we choose anything in New HL1 then the view shows all columns for New HL2, and when we select now anything in New HL2 view changes to all columns for New HL3?

        • 1. Re: Show two different type of attrition in same graph
          Jonathan Drummey

          Hi Gourav,

           

          I could see two separate-but-related issues with the calculations as you'd laid them out.

           

          - Country was a filter, so anytime you filtered on Country then it would filter all results and your overal total would be off.

          - There was nothing different in the attrition rates calculations to filter by country

           

          What I did was the following:

           

          - Created a parameter for country and included an (All) value for all countries

          - Created calculated fields for FTE Q4 2011 and Attritition to filter by the parameter

          - Created a new Attrition % for Country calculation using those fields

          - Duplicated your Sheet2 view and put in the new calculations and parameter control

          - Set the New HL2 desc filter panel to show Only Relevant Values

           

          The Sheet2 view didn't show anything for HL3, however you could add that to the view if you want and put a quick filter on it with show Only Relevant Values and that should work for you to have the dynamic update you are looking for.

           

          Let me know if this works for you.

           

          Jonathan

          • 2. Re: Show two different type of attrition in same graph
            Gourav Kumar

            Hi Jonathan,

             

            Thanks very much for helping out. Somehow this is not what I want. Let me explain again.

             

            There are 4 columns in the data(attached), for Example if we filter “Category” from the first column and then filter for “Food” in the second column, we get all the countries where food is present.

            My query is, in the above case is it possible to show the sum of all the value of all the countries where food is present (not just sum of rows where food is present in 2nd column).

            ie in the  example, after filtering “category” & “food” if Argentina is selected in the 3rd column, sum of value will be 10. But we need to show that as 986(sum of values where 1st & 2nd column is not filtered). Also if there are more than one country then sum of all people in these countries.

             

            Now using this total value for these countries I will calculate Country attrition( i.e attrition in these countries / total FTE in these countries)

             

            While my 'normal attrition' will be what is being shown by the normal filter. (i.e attrition in Food / FTE in Food).

             

            I know I am not very clear, please bear with me.

            • 3. Re: Show two different type of attrition in same graph
              Gourav Kumar

              Attached another file showing what i want in a very simplistic form, without the attrition calculations.

              • 4. Re: Show two different type of attrition in same graph
                Jonathan Drummey

                I believe what you want can be done in Tableau, just in a different way.

                 

                I think my confusion has been around the use of the word "filter." In Tableau, a filter explicitly includes or excludes data from being available to all calculations. There are a few idiosyncracies due to the order in which Tableau applies different filters (context filters before regular filters, filters on table calculations are applied last - see this comment by Joe Mako for details: http://community.tableau.com/message/139603#139603), which can sometimes be a hindrance or an advantage.

                 

                In any case, the way I'm interpreting your use of the world "filter" is that it means including or excluding data for different calculations in the same view. In that case, you generally won't use a filter on the Filter shelf unless you want to include/exclude data for all calculations. What you would would do instead is use various aggregate and table calculations to effectively do the filtering, and control those calculations through either parameters or action filters (if you are using dashboards).

                 

                This is what I did with the Country parameter in the posted workbook, so you could get Country-specific results while also showing totals for all Countries. You can create a parameter for the values of H2 in the workbook that will then be used in the attrition calculations.

                 

                Does this make sense? Hopefully I've given you enough information so you can proceed on your own, if not let me know and I'll see what I can do.

                 

                Jonathan