A few days back, WSJ ran a visualization titled “What’s your pay gap?” It depicts median pay gap between female & male workers in 422 different professions in USA. The chart uses fish eye effect to highlight the selected profession. See below demo to understand the effect.
Discussion: Is the Fish Eye Effect worth it?
In this context, fish eye effect is useless. As we have too much data, the fish eye effect becomes mere eye candy and draws attention away from important matters at hand.
That said, it is a cool idea to use with smaller data sets.
Implementation: Fish Eye Effect in Excel
Out of curiosity, I wanted to implement the fish eye effect in Excel. We can highlight selected dots using simple algebra. To stretch the chart elements to left & right requires VBA. Since VBA creates portability issues, let’s focus on the algebra based fish eye effect.
Here is a quick demo of the final outcome.
Assuming you have a bunch of X & Y values (Profession & Salary in this case), to generate the new X&Y values with fish eye effect for a particular X value (say X1),
- Define gap as an integer value, indicating the gap to either side of highlighted value at X1. For the above example, Gap = 10.
- Calculate scaling factor for the left side of Fish Eye as (X1-gap)/X1
- Scaling factor for right side of Fish Eye as (Max X – X1 – gap)/(Max X – X1)
- Use these scaling factors to calculate new X values for all dots.
- Create a scatter plot with these new X values and Y values.
- Apply special formatting to X1 & Y1 values thru another series.
- Set up a form control or VBA to change X1.
- Your interactive fish eye chart is ready.
Alternative: Closing the gaps in Pay Gap Fish Eye chart
Okay, as you can see both original and Excel implementation of Fish Eye charts are mediocre. So let’s improve them. For this type of data, we can use interactive tables so that users can filter, sort and highlight the data any way they want. This allows for flexible exploration of data.
Here is a quick demo of the interactive table style visualization of Pay Gap data.
Interactive Pay Gap Analysis Table – Construction summary
- Construct 5 pivot tables, one for each sort order.
- Create a slicer on Profession Category and link it to all 5 pivots.
- Set up a form control to figure out sort order (and dynamically fetch one of the 5 pivot table data sets, using CHOOSE formula)
- Fetch 20 rows of selected pivot table data (use scroll bar position to determine which 20 rows)
- Apply conditional formatting to the 20 rows based on criteria specified.
- Calculate summary information and display it at the top.
Here are the key ideas used during construction of this:
- Scrollable KPI Dashboard – for scrollable list.
- CHOOSE formula – for creating dynamic named range
- Conditional Formatting – for highlighting professions
- Slicers for filtering a profession
- Form controls for sorting & highlight option selection
Video: Fish Eye vs. Interactive Table – Walkthru
Here is a quick video walk thru of both approaches along with some commentary and explanation.
You can watch the video on our YouTube Channel too.
Download the workbook
Click here to download the Fish Eye effect workbook. It contains both fish eye and interactive table visualizations. Examine the calculations tab for all the formulas.
How would you visualize this data?
Do you like the Fish Eye effect chart? How would you visualize this data? Please share your thoughts and implementations in the comments.
More case studies
Check out below tutorials and case studies to learn how to visualize complex data in Excel.
13 Responses to “Using pivot tables to find out non performing customers”
To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales). Now I can show more than one year, I can summarize - I can do many more things with it. ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format. Much easier in my opinion.
David
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
David, I was just about to post the same!
In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
Rgds,
Chandoo,
If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :
=IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))This formula will sum the sales from Selected Year to 2012.
JMarc
If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
Regards
I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
Change the helper column to:
=IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.
Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
Cheers
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious? I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
Thanks again
worked it out thanks...
when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated
Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.
Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula
=2000+MATCH(1000000,E5:P5)
will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.
Somewhat longer but perhaps a bit more solid (with the column titles in row 4):
=RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)
[…] Finding non-performing customers using Pivot Tables […]