Tags: COUNTROWS

Determine latest condition of each equipment and show a month wise count

{ 0 Comments }

There are 100 machines in a factory.  Every machine has different test frequency. In a given month, not every machine is tested but we still have the last known rating (from some previous month) of that machine.  We have to show the latest rating of each machine for each month in a stacked column chart. […]

Read More →

Count tasks by status

{ 0 Comments }

Assume a simple 3 column dataset as shown below – the date of each task and the status of that task. The objective is to get the status wise count of tasks by the last time stamp.  So for the Status “To-do”, the count should be 2 – Task ABC and DEF.  Only these two […]

Read More →

Segment customers into dynamic buckets

{ 0 Comments }

Consider a 4 column table – Respondent ID, Device ID, App Name and Category.  So this dataset shows which apps are installed on which device ID by which user and which category do the apps fall into.  It is a small dataset with only 4 columns and 2,000 rows. The question on this dataset is […]

Read More →

Customer analysis by Country and time period

{ 2 Comments }

Here is a Sales dataset of 8 columns and 29 rows.  It basically details the revenue earned and cash collected by service type, Customer, Country and Period.  For a selected Country and time period, there could be customers availing of both services or of any 1 service. There are 2 broad questions that one may […]

Read More →

Perform an aggregation on Top x items after satisfying certain conditions

{ 0 Comments }

Visualise a 5 column dataset as show below.  This is a very small sample of the actual dataset.  It shows the date on which supplies were received for each item from Vendors and whether those supplies had errors in them.  Finally those identified errors have been bucketed into relevant categories.  The Item ID# is a […]

Read More →

Split total patient hospitalisation days into multiple months

{ 0 Comments }

Here’s a dataset with 3 columns – Patient Name, Date of admission and Duration (days). Patient Date of admission Duration (day) A 10-10-2017 25 B 20-10-2017 6 C 23-10-2017 12 D 29-10-2017 9 The objective is to split the hospitalization per patient into different months to determine each month’s revenue accrual.  The expected result is […]

Read More →

Determine the total number of projects by Status

{ 0 Comments }

Here’s a simple 3 column table showing Date, Project name (Cat.) and Status of the project.  Each project can have multiple status entries on different dates.  So as you can observe, project “alpha_9383993” was In Progress on Oct 2, 2017, remained so on October 5, 2017 but was completed on October 6, 2017. Date Cat. […]

Read More →

Alter the behaviour of a filter/slicer from OR to AND

{ 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 →

Perform a Competitor, Feature and Customer Analysis with the PowerPivot

{ 2 Comments }

Assume there are four interrelated tables. One may want to create a pivot that allows one to filter data by using the slicers. Data should be filtered by the following interdependent slicers selections: Customer, Country and segment.  The logic behind the pivot when using the slicers shall be as follows: 1. Feature N is only shown if relevant to Customer X in Segment […]

Read More →

Compute year on year growth in a Pivot Table

{ 0 Comments }

Assume a three column dataset which has Year, Company ID and Cash flows.  For each Company, there are cash flows for multiple years.  So for Company ID A001, there are 7 rows, one each for 2010 to 2004 and cash flows appearing in a third column.  Let’s assume the number of rows are 750,000. The task is to compute […]

Read More →