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.

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)
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:

 Item Start Category End Category Change Category 1 Green Red G to R 2 Green Yellow G to Y 3 Yellow Red Y 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

• ###### 1. Re: How to create a pie chart that counts the number of items that changed from one category to another.

Hey Joe,

I put together the attached workbook as a potential solution using LOD calculations. I think I addressed the crux of the issue, getting the first-last by Proposal ID. If you wanted to include some logic that would identify the improvements, you could build off of my 'First-Last' calculation using similar elements. Let me know if you have other questions.

Best,

Jimmy

InterWorks

1 of 1 people found this helpful
• ###### 2. Re: How to create a pie chart that counts the number of items that changed from one category to another.

Hi Jimmy,

Thanks so much for your help!

Your calculation seems to capture exactly what I was looking for.

As a token of my appreciation, please enjoy this scene from Grind that might tickle your fancy! Jimmy_was_Jimmy - YouTube

Happy Vizin'!

Joe

• ###### 3. Re: How to create a pie chart that counts the number of items that changed from one category to another.

Hahah appreciate the clip! Glad I could help out.

Best,

Jimmy

InterWorks

• ###### 4. Re: How to create a pie chart that counts the number of items that changed from one category to another.

Hi Jimmy,

Just following up!

Your reply really helped me with my analysis, but I do have a second question:

Would you be able to perform a similar series of calculations when comparing a sum of values?

I'm still seeking the start and end values for each item (fruit, as shown in the sample data below), but need to aggregate the values first! Ideally, I'd be able to label each first day as either 'High' (above 50) or 'Low' (50 or less) and then be able to categorize their change as either: (a) 'H - L' or (b) 'L - H'.

The Raw Data:

 Fruit Day Nibbles Orange 5/4/2016 12 Orange 5/4/2016 8 Orange 5/4/2016 11 Orange 5/4/2016 5 Orange 5/4/2016 12 Orange 5/5/2016 2 Orange 5/5/2016 11 Orange 5/5/2016 6 Orange 5/5/2016 17 Orange 5/5/2016 19 Orange 5/5/2016 11 Orange 5/5/2016 14 Orange 5/5/2016 18 Orange 5/5/2016 17 Orange 5/6/2016 17 Orange 5/6/2016 11 Orange 5/6/2016 15 Orange 5/6/2016 6 Orange 5/6/2016 11 Orange 5/6/2016 16 Orange 5/7/2016 7 Orange 5/7/2016 12 Orange 5/7/2016 13 Orange 5/7/2016 8 Orange 5/7/2016 14 Orange 5/7/2016 3 Orange 5/7/2016 19 Apple 5/4/2016 6 Apple 5/4/2016 20 Apple 5/4/2016 8 Apple 5/4/2016 12 Apple 5/4/2016 13 Apple 5/5/2016 4 Apple 5/5/2016 2 Apple 5/5/2016 4 Apple 5/5/2016 15 Apple 5/6/2016 17 Apple 5/6/2016 4 Apple 5/6/2016 13 Apple 5/6/2016 15 Apple 5/6/2016 13 Apple 5/6/2016 12 Apple 5/7/2016 4 Apple 5/7/2016 6 Apple 5/7/2016 9 Apple 5/7/2016 13 Apple 5/7/2016 1 Apple 5/7/2016 2 Apple 5/7/2016 10

The Sum Table:

 Row Labels 5/4/2016 5/5/2016 5/6/2016 5/7/2016 Grand Total Apple 59 25 74 45 203 Orange 48 115 76 76 315

The Result Table:

 Change Type Count H - L 1 L - H 1

I tried re-purposing your LOD calculations to no avail and am thoroughly stumped on how to tackle the aggregation!

Let me know if this makes sense!

Best,

Joe