4 Replies Latest reply on May 4, 2016 10:10 AM by Joseph Sutkowski

    How to create a pie chart that counts the number of items that changed from one category to another.

    Joseph Sutkowski

      Hi all,

       

      First time posting here!

       

      What I want to do:

       

      I want to create a pie chart that shows how many items (I call them 'Proposals' in my workbook) started out in one category and ended up in another. Ideally, there would be four categories representing these changes: (a) "G to Y", (b) "G to R", (c) "Y to R", and (d) "Unchanged or Improved".

       

      What I tried:

       

      1. Used window calculations to determine the FIRST value of each item (Proposal) | Source: Calculate difference in value based on two date parameters
      2. Used window calculations to determine the LAST value of each item (Proposal) | Source: Calculate difference in value based on two date parameters
      3. Used IF statements to categorize the FIRST value of each item (Proposal)
      4. Used IF statements to categorize the LAST value of each item (Proposal)
      5. Used IF statements to categorize the change from first to last for each item (Proposal)

       

      Example:

         

      ItemStart CategoryEnd CategoryChange Category
      1GreenRedG to R
      2GreenYellowG to Y
      3YellowRedY to R

       

      The table above should spit out a pie that shows 1 "G to R", 1 "G to Y", and 1 "Y to R".

       

      Where I'm stuck:

       

      I can't figure out how to count and display the total number of items (Proposals) per my newly defined categories (from step 5 above) into a pie chart.

       

      What I'm thinking of trying:

       

      I'm wondering if a LOD calculation might be more appropriate, but am not too familiar with their implementation/use cases.

       

      What I've attached:

       

      • Raw data Excel file
      • Workbook that shows change of items (Proposals) over time. Reference lines were added to denote what constitutes each category: "Green" > = 90, 70 = < "Yellow" < 90, "Red" < 70.

       

      Any tips and/or hints would be much appreciated!

       

      Best,

       

      Joe