Date Range Calculation Problem

Discussion created by MarcDolley on Jul 21, 2016
Latest reply on Jul 22, 2016 by ron.harris

This is probably going to be hard to explain, so please bear with me.


Background: My client has asked for a report which shows the sales per product (there are currently only five) for two specified periods (current and previous) with the differences between the two also calculated. They want to generate the report based on one of four specified periods (Fortnightly, Monthly, Quarterly and Annually). Three of those (Fortnightly, Monthly and Annually) are fairly easy when it comes to determining the two date ranges.


With the fortnightly option, they simply select an end date from a drop down calendar and the first date range is that date minus 13 days (June 15 - 13 = June 2, therefore 2/6/16...15/6/16 (excuse the Australian date formats). The second date range is the day before the first date range minus 13 days (19/5/16...1/6/16).


The monthly option is also quite simple because they're selecting a calendar month (eg June) from a drop down list and entering the year. It's then easy enough to determine the date range for that month as well as the previous calendar month.


Similarly, the year ranges follow a similar path. They enter a date for the year ending and the rest is easy to calculate.


It's the quarterly option which is giving me grief. At the moment, I have it set up so, like the monthly option, they select a month and year and I can work out the two quarter date ranges. That works perfectly. However, they have asked to be able to specify any date for the end of the quarter (eg June 15 2016) and have it work out the date ranges from there. The reason I'm having problems is that quarters could span two years and months have different numbers of days (don't even mention leap years). Here's a couple of examples of the results I need to produce (but the entered date could be any date):


Selected Date: 15 June 2016 Range 1 (Current): 16 April 2016 to 15 June 2016 Range 2 (Previous): 16 February 2016 to 15 April 2016

Selected Date: 1 January 2016 Range 1 (Current): 2 October 2015 to I January 2016 Range 2 (Previous): 2 July 2015 to 1 October 2015


I don't believe there is a simple way of working this out, but if anyone can think of one, I'd love to hear it.


I should add that I've simplified the overall requirements to make answering the question easier. There are actually four sets of figures required for each product for each range (quantity, average price, average margin and growth) as well as two sets of data for each period for the top five and bottom five customers and a few other figures thrown in for good measure. As always, the client has no clue as to what's involved is showing the result.