Pivot tables - Complexity Demystified - Learn in 5 minutes

Earlier I had mentioned Pivot tables as simple. Now, I am just going to explain how simple it is. I will use my earlier example with dummy variables, some sample fields.

I am given lots of data, and I need find out the number of Employees in my company & their skill set and some other variations.

A shortened sample table below:

Create Pivot choosing all the rows and columns. In Excel 2003, It would be Data > Pivottable and go through the 3 steps. Finish by saying you want to put in a new sheet. Now the new sheet looks like this:

From the data,
1. I need to know the number of employees skill set wise.
Logic: Employees - skill set wise - number
From Pivottable FieldList to the Page field - drag&drop Type
From Pivottable FieldList to the Row field - drag&drop Job Role-Skillset
From Pivottable FieldList to the Data Items - drag&drop EmpCode (We need to have unique items as 2 employees can have same name)
Select Regular from the Type field.

Below screenshot will show employees - skill set wise.

2. In above sample, I want to know Employees on Bench.
Logic: Benchwise - Employeewise - skillset wise - resource number
In the above, From Pivottable FieldList to the Data Items - drag&drop Resource Status to Page Field
Select "Bench" from the Resource Status drop down.
Result is as below:

3. A little more complex, I need to know the number of employees, skillset wise and experience.
Logic: Employeewise - skillset wise - Experience wise - resource number
First in the Resource Status, change in the drop down to All
From Pivottable FieldList to the Data Items - drag&drop Exp (Yrs) into the Column field
Result is as below:

I have attached the sample file below, for others to have their individual workouts. Select File > Download.
 Sample File: https://docs.google.com/file/d/0Byuaop2Om8yRcXpWdWI5Z3EzTWc/edit?usp=sharing

Other Analytics that can be derived from the data given:
4. What are the number of employees reporting to different managers?
5. What is the skillset available to every manager?
6. What is the location wise skill set availability?
7. What is the location wise - people on bench?
8. What is the location wise - people on LOA? 
9. Which location employs the most contractors? And for what skill set?

Right Approach to the problem is the key to the demystification of the complexity.

Smart Learning - better and faster

All traditional approaches to learning is from starting with low complexity and moving on to high complexity. This is a tried and tested methodology which is followed in all organizations worldwide.

This method, I would rather consider it as a base method for just 1 language which we can keep as base language so that we are able to understand the constructs.

If I consider the base language as Java, there is much similarity between Java and C, C++, VB, VC, flex, PHP, etc when approaching from the construct and principles. The technique of resolving is different in all languages.

If we have 1 strong base, I believe it is sufficient foundation. For any new learning, we should be  jumping right into the middle of complexity and learning in both directions of upward and downward. Also, I would place more emphasis on a practical resolution rather than theoritical. Practical examples would be taken from current ongoing projects by creating a shadow structure and live programs used.

And finally support, after completion, it will be of immense help to reference back to a wiki and link back to Subject Matter Experts (SMEs) for ongoing queries and learning.

If I may take a traditional method of teaching at 2 weeks, using the deep dive method, we can learn a language in less than 3 days.

I have applied this method several times in my life and I find it to be the best.

I had tried for several years to learn the Pivot tables through traditional methods. I had read several books, but always found it complex to understand. Some of my friends informed they would teach me, but it never happened. I understood, knowing the Pivot table makes them desirable as a top manager. Around 5 years back, I was faced with a situation which required that I have to do Pivot or else it is going to take a looong time!

I was required to use huge data dump, and bring out information as to the number of employees in India having different skill sets. The data had over 50,000 people and skill sets were over 100. On that day, I am happy to announce I had understood Pivot tables in less than 15 minutes. I dragged and dropped, and lo ... it was complete! I also understood the logic behind the Pivot table which I have taught several colleagues. It took me only 5 minutes to teach them!

I will certainly cover in this topic in my next post.

That data was earlier given to my colleague and he had taken more than 5 days working traditional style trying to gather data and he was still unable to compile. He had finished close to 5000 resources in 5 days.

To sum up:
1. Strong Base of 1 language
2. Jump into the middle of the complex language and learn both ways
3. Practical application (shadowing of running projects)
4. On going support network (wiki's, SMEs)

Smart in Everything

The key word of these days is the abbreviation "SMART". Who among us have not come across these abbreviation tagged to different objects making them a desirable commodity. Wished to cover a whole range of smarter way of accomplishing things through this blog.

Most of us are not born smart but we do become smart. In some situations, we may realize Smartness...Ouch ! That Smarts!...and then we become Smart.

Being Smart...may lead us to innovations and doing things differently but efficiently. Some situations which I would cover in my later posts:
How to perform more work in less time.
How to reduce cost / spending
Approaching purchasing decision practically 
Productivity improvements
Complexity demystified
Learning better and in a shorter time
Individual contribution to a Smarter & Greener Planet
Utilize better, Reuse & Save

This blog contains my thoughts on this subject which I have learned and understood during the course of this life's journey. It may also have references to Smart people, Smart devices, Smart articles (I will credit the Smart Author of course!), Smart way of doing things, Smart learning, Smart eating, Smart exercises, Smart Life, Smart businesses, Smart Tools, Smart Earth, Smarter Purchases and all items that are Smarter!!!

I will try to keep the language and approach as simple as possible. That I believe will fuel to a lot more smarter people accomplishing smarter things.