About Me

My photo
R.K.Laxman's Common man!

Identifying level for description of IMS data in Excel

This post/ tip is for Pharma market research professionals who use IMS data.
IMS Health uses EphMRA (European Pharmaceutical Marketing Research Association) classification system across the world while producing data.

These codes when downloaded from some IMS Data sources are demarcated by several "empty spaces" before each code/description. As the excel download of the data will have hundred of rows, identifying the level through a formula will make analytics easy.
Here is a simple solution using excel formulae.
You can download the excel file here or @Downloads.

Create Stunning Timelines in a Jiffy on PPT


Show timelines in presentations with a Style! 
Bring in the WOW factor.
Here is an Office add-in you can use to create stunning and great looking timelines. 
The add-in is absolutely free and can be downloaded here. It has an easy to use interface and wizard that helps you create timelines in a jiffy! Download Office Timeline add-in here

With a Free edition, you can create an unlimited number of timelines, Change, Edit them on the fly.
It's absolutely free, easy to use, creates stunning timelines on a PowerPoint. I recommend It.


Watch the Timelines in Action here

Cartograms - It's a new World out there...

A couple of days back, I had an idea to create World Map based on variables such as Population, GDP, Per capita health care expenditure etc.
I was excited...I thought I found a new visualization model...I dreamt of  ...name.....fame...new contribution to the world of visualization...
Then, I did a little search on Google. I understood such visualizations are in vogue and are called Cartograms...
In the end, I cursed that someone copied my idea even before I could conceive it...
However, I have come to terms with it...

 Let's see what these Cartograms are..
For those who are familiar with Bubble Charts, understanding Cartogram is easy...
Cartograms are simply maps of the world wherein the size of an entity like a country or a Continent is proportional to the size of the variable being depicted.
Let me give you an example.

 If we were to create a Cartogram of the world based on the Area of each country, you will see the Map exactly like the world map we see... Like the one on the right side...

If we were to create a cartogram of the world based on the Population of each Country, you will see the map like this. on the left side...

 You will see the world from a different viewpoint...
World Mapper is one such brilliant site that Created innumerable Cartograms. Many are available for Free download as Posters...



Maps by Mark Newman, data by Danny Dorling, text by Anna Barford, quality control by Ben Wheeler, website by John Pritchard, poster design by Graham Allsopp, and individual country maps by Benjamin Hennig.
There are few software programs to create Cartograms.
Visit Cartogram Central for exploring the options.

 I tried to recreate the same on Excel with varied results.
I will share with you the template once I have a fair amount of success. Share with me if you have any ideas on creating Cartograms in Excel.
Till then, enjoy World Mapper.
For more information on Cartogram, click the link below
Cartogram Types

Numbers numbers all the way!

You often hear statements like "Our company has 2 billion dollar turnover"
Do you know what a billion is ? Can you write down the number ?
I guarantee that most can not write this most often used number .I have an easy way to remember!

1,000 is 10 to the power of 3 - so to say one followed by 3 zeros 
                 i.e 10 X 10 X 10 written as 10^3

1,000,000 is 10 to the power of 6 is  Million....
1,000,000,000 is 10 to the power of 9 is a Billion 
1,000,000,000,000 is 10 to the power of 12 - Trillion

The trick is remembering  the 3 Table
  • 10^3 is Thousand,
  • 10^6 is Million,
  • 10^9 is Billion,
  • 10^12 is Trillion and
  • 10^15 is Quadrillion and so on....

Here are some fun facts:
Zillion : Means a very large number. This has no defined Mathematical meaning!

Googol : This is the number from where Our "Google" name originated!
googol means 10^100 ie 10 to the power of 100!, one followed by 100 zeros!

Googolplex :  Means  10^Googol ie 10 raised to the power of Googol
in Mathematical terms it is 10^10^10^2
I can not write this number @#$!%^&  
Sure, I am not going to get a job in a company that has a Googol or Googolplex dollar turnover!

It's You

Tree Maps

Have you ever heard of Tree Maps? In fact, a Tree map is a Big Brother of both Bar Charts and Pie Charts.
Tree-maps are a complex but powerful information visualization technique.
They were introduced in Shneiderman, 1992. Tree Maps are used to visualize hierarchical data as a set of nested rectangles.
As mentioned, the concept of tree-maps is basically from Ben Shneiderman an American computer scientist from University of Maryland as a solution to his problem of understanding and visualizing who is using how much of disk space ( It was 80 MB  of disk space shared by 14 people in those days ! )
You can read a wonderful article by Ben Sheneiderman titled " Discovering Business Intelligence Using Tree-map Visualizations.
Tree maps can show relatives size of each component in the hierarchy and also use color coding to show another attribute. Here is an example of a simple tree-map .
This picture is taken from Wikipedia
The size of Rectangle denotes the waiting time for patients in NHS Primary care Trust in UK.
There is  another variant of Tree maps that uses circles instead of rectangles. Someone called it as pebbles. Here is an example of a pebbles Chart.
This shows GDP Per capita of countries in PPP Terms in $ International
This is the same data in a Tree chart
Creating Tree-maps is a Complex work and need considerable skills . Here I provide you two easiest ways to create Tree maps.
      1. Use Microsoft Excel
      2. Use ManyEyes, an IBM Initiative to create and share data visualizations on Web
Visit my earlier Post on Many Eyes : Text Visualization-Many Eyes
Microsoft  Research Provides a Free excel Add-in, that makes the job of creating Interactive Tree Maps in Excel easy.
Here is the link to download Excel Add-in
1.Tree Maps Free Excel add in - Link 1

2.Tree Maps Free Excel Add in - Alternative Link from Microsoft Store.
for more advanced users who can handle xmls , Here is a site to download a Java based Tree visualization Programme. Here is the Link
http://www.randelshofer.ch/treeviz/
According to the web site,
The project currently consists of a file browser demo, which visualizes the file system with the following tree diagrams:
  • Hyperbolic Tree
  • Circular Tree-map
  • Rectangular Tree-map
  • Sunburst Tree
  • Icicle Tree
  • Sunray Tree
  • Iceray Tree
Try few Tree maps now....

Beware... Busy Executive

I wish to draw the attention of  Busy Executive to the three most common pitfalls in using ... Google, Linked In and  Spell Check.

Inspiration from Google ;-)
Many a time you receive Motivating emails, Impressive quotations, elegantly crafted CVs, Well drafted letters and Reports with linguistic gymnastics.
Copy a couple of sentences and Google them. Bingo. 
You will reach the original article! The right word is Plagiarism.
It’s not uncommon for a busy executive to copy content from the Internet and create presentations and documents. The same is true while using documents from your colleagues.
Whenever you are dealing with office documents, you must remember, the official documents carry with them their properties.
What are the properties of a document?
Go to FILE in the menu on the document >> Click on PROPERTIES
You will be amazed to see the extent of information available.
Details like TITLE, AUTHOR may cause embarrassment if you have copied someone else’s file and updated the same file.
Change the author's name and other properties if you wish to avoid embarrassments.
Maybe many are not aware of the document properties… However, YOU must know!
Imagine if you had copied the CV format of your colleague and just changed the contents here and there and it goes to an HR guy who knows how to check the properties of a document!
The embarrassment is completely avoidable.
LinkedIn
LinkedIn… it's powerful with more than 175 million users. 17 Plus are from India!
India rocks with second-highest users after the USA! 17 Plus Mio are from India!
If you wish to use LinkedIn for “personal branding”, do not forget to…

Be visible…add Photo, complete profile in all respects.
Recommendations …get them.
Add value …join groups and participate in discussions.
Network … connecting is not collecting…
Differentiate yourself by demonstration of skills through Slide share and Bolg link..

 Though certain exposure is important, overexposure may be counterproductive! If you want to protect your privacy, change the settings so that, you can control who can see your updates!
Click to enlarge
Spell Check :
Today’s executives in most corporates are either Gen X or Gen Y who were brought up in the internet milieu and very few have learned traditional, almost extinct typewriters! 
The result is a high degree of dependence on computers!
With extensive use of computers, you are vulnerable to bloopers!
Blooper!
Complete faith and trust in spell checker!
A spell checker can guarantee you "correctly spelt words in a wrong context!"

 sea this example 

Dear Mad am,
I am Robert. This has reference to the telecon we had this morning regarding the role of communication export. I understand that man ageing communications is knot a joke. I am glad to inform that I attained expertise in this field as manger communications as I lead a team. I am known to be an excellent pubic speaker. would love to meat you in person to discus the some issue. I request you to send me the data and time for meeting. Please do give a massage to confirm the same. Mr. James, the HR executive must also be waiting for this massage to insure that he is present during the meeting. Please piss on the information. The summery bellow should help you prepare for the meeting. I wish to inform you that seven mules and one female colleague from our department will also be cum and  attend the meeting.
Warm regards,
Robert
Not even a single spelling error! WOW!
All errors are typo errors! 
Most often, the problem is homophones ,similar sounding words like “their” and “there” and also with similar spelling words like “manager” and “manger”.
Check the list of British English Homophones
However, Good News is here....
Office 2007 and 2010 introduced Contextual spell checker!

 Follow these steps to activate the contextual spell checker:
Step 1: Click office button
Step 2: Open Word Options
Step 3: Click Proofing

Step 4: In the When Correcting Spelling and Grammar in Word section, select the Use Contextual Spelling check box. Now Click OK. That's it!
God bless the team and Bill Gates!

By the way. Here though this deals with issues like time management and prioritization,Focus-energy, here is a 2002 publication from HBR titled Beware The Busy Manager. 
You can download this HBR article.