Assume a 4 column dataset (a small sample) as follows: City of Origin City of destination Mode of Transport Passengers travelled New Delhi Pune Air 123 New Delhi Mumbai Air 213 New Delhi Kolkata Air 125 Chandigarh Jammu Bus 785 Chandigarh Amritsar Train 567 Given this dataset, one may want answers to the following questions: […]
Read More →
In this workbook, I have Sales data of an E-Commerce Company for 3 months. The typical columns in the base data are: 1. Order Date/Time 2. City to which orders were shipped 3. Order Number 4. Payment Type i.e. Cash on delivery, Net Banking, EMI’s 5. Order Status i.e. Delivered or cancelled 6. SKU’s which the […]
Read More →
{ 0 Comments }
Imagine a three column dataset as shown below ID Prize Year A X 9 A Y 10 B X 9 B Y 9 B Z 10 As you can observe, there are duplicates that appear in each column. The task it to convert this tabular data structure to a matrix like one with ID’s appearing […]
Read More →
{ 0 Comments }
Imagine a dataset as shown below Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5 Suppose the data is in range A3:C7 (headings are in A2:C2). The objective is to add numbers where the following conditions are met: 1. Status should be Active; and 2. Text […]
Read More →
{ 0 Comments }
Here’s a simple two dataset: Project ID Employee A00 Ashish Mathur A00 Henry A00 Paul A00 Sanjay Singh L02 Geeta Puri L02 Ajay Singh L02 Robert L02 Mike L02 Ajay Kumar Venkatesh L02 Ashish Mathur L02 Geeta Kukreja A00 Sameer Mehta L02 Harish Rai As one can observe, there are repetitions in the Project ID column. […]
Read More →
Assume the following data layout Name Budget April Actual April Budget May Actual May Budget June Actual June a 2 1 1 4 3 b 4 4 2 1 c 2 3 3 3 d 2 1 e 5 6 4 8 6 As one can observe here, there are two sub columns for each […]
Read More →
Here is a dashboard created with a Pivot Table, a Pivot chart and slicers (Click to enlarge image). In the Pivot Table, the % have been computed using “% of row total”. The Pivot chart shows two columns per month – one for complete and the other for incomplete. The objective is to show only the […]
Read More →
{ 2 Comments }
Imagine a 4 column dataset as shown below: Resource Name Start End Projects Raghav 02-06-2015 05-06-2015 p4 Raghav 02-06-2015 07-06-2015 p5 Raghav 08-06-2015 12-06-2015 p1 Raghav 16-06-2015 19-06-2015 p2 Raghav 22-06-2015 26-06-2015 p3 Ashish 03-06-2015 09-06-2015 p6 Ashish 04-06-2015 07-06-2015 p7 One may want to transform this dataset to the below mentioned one. There will […]
Read More →
Affinity analysis encompasses a broad set of Analytic techniques aimed at uncovering the associations and connections between specific objects: these might be visitors to a website (customers or audience), products in a store or content items on a media site. Of these, “market basket analysis” is perhaps the most common example. In a market basket analysis, one analyses combinations of […]
Read More →
{ 4 Comments }
Hi, Assume a column of names as follows: Name Mohammed Zia-Ul Haque Steven Thomas – ,-Rohit Sunil Ahir-Chowdhary.- Anuj ———– Sameer — ..,Mohit — Rajeev Nair. Monalisa . Das Vijeta … –,.Anjana. M.U..,- Please observe that there are special characters before the name, within the name and after the name. The task is to remove […]
Read More →
Visualising data flows using Custom Visuals
{ 0 Comments }