Show sales only for corresponding months in prior years

{ 2 Comments }

Refer to this simple Sales dataset The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear […]

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 →

Compute transaction fee based on a tiered pricing model

{ 0 Comments }

Consider a simple dataset as shown below: For each tier, the tier rate is incrementally applied to the volume within the tier volume range.  Given the following transaction volumes, one may want to compute the transaction fee The expected result is shown below As one can observe, for a transaction value of 400,000, the fee […]

Read More →

Determine the most recent status after satisfying certain conditions

{ 0 Comments }

Assume a three column dataset with Patient ID, Smoking Status and Review Date PatientID SmokingStatus ReviewDate P1 10-03-2018 P1 9 09-03-2018 P1 1 08-03-2018 P1 4 07-03-2018 P2 9 10-03-2018 P2 9 09-03-2018 P2 9 08-03-2018 P2 9 07-03-2018 P3 2 10-03-2018 P3 09-03-2018 P3 9 08-03-2018 P4 9 10-03-2018 P4 1 09-03-2018 P4 4 […]

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 →

Compute the average of values against the 5 most recent dates of each Category

{ 0 Comments }

Here is a simple 3 column dataset showing Categories, Date and Value Catagorie Date Value Fish 08-12-2015 6 Crab 05-12-2015 7 Crab 04-12-2015 6 Bird 27-11-2015 4 Snow 25-11-2015 10 Cat 21-11-2015 7 Dog 12-11-2015 5 Dog 28-10-2015 5 Fish 12-10-2015 3 Bird 11-10-2015 9 Dog 22-09-2015 9 Crab 17-08-2015 8 Cat 11-08-2015 1 Fish […]

Read More →

Combine unique entries from a range of cells after satisfying a condition

{ 2 Comments }

Here is a simple three column dataset showing Item ID, Catalogue Name and Catalogue Year Item ID Catalogue Name Catalogue Year A Spring 2012 A Spring 2016 A Spring 2017 B Fall 2014 B Fall 2014 B Fall 2016 The second and third last rows are clear duplicate rows. The task is to create a […]

Read More →

Restructure the layout of datasets

{ 4 Comments }

I’d like to discuss 2 cases of restructuring layouts of datasets: Case 1 Consider the 7 columns dataset below.  For every Doc Number, there are 6 columns – 3 columns for the name of the employee and another 3 columns for the Percent of each employee. Doc Number Emp 1 Emp 1 % Emp 2 […]

Read More →

Prepare an invigilation schedule for each teacher by different time periods

{ 5 Comments }

Imagine a multi column exam invigilation schedule with the following information S. No. Name of staff Designation of staff member Two columns for each day on which there is an exam – one for Morning and another for Afternoon A * under each column if that particular staff member has to be an invigilator during […]

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 →