# Tags: DATESBETWEEN

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 →

In this simple 3 column dataset, there are 2 accounts – Konto 1 and Konto 2.  Each account has a balance outstanding as on a certain date.  However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020. The objective is show the balance outstanding every day.  For days which […]

Read More →

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 →

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 →

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 →

In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years – 2017 and 2018. The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month.  So, if a user selects the Year as 2018, […]

Read More →

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 →

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 →

Assume quantity sold date by date and City in a three column database.  The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table. The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running […]

Read More →