Here’s a simple dataset showing the Date of sale, Customer Name and Sales amount. Date Customer Name Sales amount 12-03-2017 A 1 12-03-2017 A 2 12-03-2017 A 3 12-03-2017 B 4 12-03-2017 B 5 12-03-2017 B 6 12-03-2017 B 7 12-03-2017 B 8 13-03-2017 A 1 13-03-2017 A 1 The objective is to determine Customer […]
Read More →
{ 9 Comments }
Here is a dataset showing Project wise forecast of open opportunities. Topic is the Project Name Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client Duration is the time (in months) for which the project would run Amount is the total amount […]
Read More →
{ 3 Comments }
Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B. My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18. I’d like to exclude employees who: Did not work on both these days; and Worked on both […]
Read More →
Imagine a Sales dataset with the following columns – ID, Country, Start date, End date and Tier. Here’s a snapshot of the table: ID Country Start date End date Tier 33948 ES 25-Sep-17 28-Sep-17 3 19820 US 08-Oct-17 17-Oct-17 4 9118 US 27-Oct-17 03-Nov-17 2 1563 ES 02-Sep-17 07-Sep-17 2 11087 US 18-Oct-17 27-Oct-17 1 […]
Read More →
Imagine a fixed monthly amount due to an Organisation for services rendered to various customers. While an invoice is raised every month by this Organisation, not all pay up the dues on time. For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum. The objective is to determine cumulative […]
Read More →
Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation: Learner Stage completed Course Bill Stage 1 Public Speaking Bill Stage 2 Public Speaking Bill Stage 3 Public Speaking Susan Stage 1 Effective Communication Bob Stage 1 Public Speaking Bob Stage 2 Public Speaking Sheila […]
Read More →
{ 4 Comments }
Assume a simple two column dataset with dates in column A and numbers in column B. The dates in column A are from January 1, 2013 to December 31, 2016 and numbers in column B are for the period January 1, 2013 to December 31, 2015 (there are no numbers for January 1, 2016 to […]
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 →
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 →
{ 0 Comments }
Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix […]
Read More →
In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof
{ 0 Comments }