Tags: ALL

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

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

Analyse membership changes from year to year

{ 2 Comments }

Assume a simple 4 column dataset as shown below.  This data shows which ID had which type of subscription in which year.  So ID A, which started as a “Free” subscriber in 2018 switched to a “Premium” subscriber in 2019 and then churned out in 2020.  Likewise, ID D which started as a “Pro” subscriber […]

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 towns according to volume contribution and market share with a slicer

{ 0 Comments }

This post is an extension to the one I posted here – Segment towns according to volume contribution and market share. Here’s a simple dataset of Shampoo sales in the state of Rajasthan, India. For a chosen segment, one may want to segment the 4 towns based on the following conditions: Based on the two screenshots […]

Read More →

Segment towns according to volume contribution and market share

{ 0 Comments }

Here’s a simple dataset of Shampoo sales in the state of Rajasthan, India. For a chosen segment, one may want to segment the 4 towns based on the following conditions: Based on the two screenshots shared above, the desired result is shown in the screenshot below: The desired result is shown in range E16:E19 and […]

Read More →

Calculate rolling sum for the past week by ignoring blank cells

{ 0 Comments }

Assume a simple dataset as shown in the image below (the input data is in columns A and B only.  The desired outcome is in columns C and D). The objective is to calculate the 7 days rolling sum and average (as shown in columns C and D) ignoring blank cells.  So in cell C8, […]

Read More →

Analyse free flowing text data or user entered remarks from multiple perspectives

{ 0 Comments }

Here is a 2 column dataset – UserID in column A and Remarks in Column B.  This dataset basically tabulates the remarks/comments shared by different users.  Entries in the Remarks column are basically free flowing text entries which have the following inconsistencies/nuances: Users reported multiple errors which are separated by comma, Alt+Enter (same line within […]

Read More →

Show Project wise status in a Pivot Table

{ 0 Comments }

Visualise a simple 6 column Table as shown below – Project Name and the finish date for each of the 5 stages that the projects go through.  Each project goes through 5 stages – Requirement (Req), Development (Dev), UAT, Implement and Warranty. The objective is to report on the status of each project at the end […]

Read More →

Identify Customers that Organisations can upsell or cross sell their products to

{ 0 Comments }

Here’s a simple Sales data of a retail Store which sells Apple Products.  Since a customer can transact multiple times, there will be repetitions in the Cust ID column.  While Cust ID 123 and 782 purchased multiple products from the same Store in one transaction, Cust ID 53 purchased multiple products from different stores (Store […]

Read More →

Filtering on 2 date fields within one Table

{ 0 Comments }

This table contains a list of all the inspections created and completed within different time periods. The objective is to create two Tables from this single table – one showing the Accounts created within the chosen time period and another showing the those that were closed within the same time period.  Here are screenshots of […]

Read More →