13 Replies Latest reply on Jul 22, 2016 2:09 PM by ron.harris

Date Range Calculation Problem

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.

Marc

• 1. Re: Date Range Calculation Problem

Let ( [ m = month ( yourtable::yourfield ) ;

Y = year ( yourtable::yourfield ) ;

d = day ( yourtable::yourfield ) ] ;

Date ( m - 2 ; d + 1 ; Y )

) // let

this will work even if m-2 produces a negative value.

1 of 1 people found this helpful
• 2. Re: Date Range Calculation Problem

Someone may want to adjust the result on 30 April 2016 etc. as OP wrote "different number of days".

• 3. Re: Date Range Calculation Problem

Thanks Phil. It's really close, but not exactly right. First up, the part Date ( m - 2 ; d + 1 ; Y ) should read Date ( m - 3 ; d + 1 ; Y ). It was only giving me a two month period. Once I changed it to three, it worked. Except there are a few anomolies. For example, if I enter April 30 (the last day of April), I would expect it to give me February 1, but the calculation returns January 31. Similarly, if I enter 29 February, it returns November 30 instead of December 1. So there does seem to be a slight problem with shorter months.

Ordinarily, I'd say that it's only one day and it doesn't matter, but I have to leverage off that date to find the matching previous period which could then end up being two days out.

I appreciate your response. The solution is simple and elegant, but it also highlights the complexity of the problem. Dates are dumb.

Marc

• 4. Re: Date Range Calculation Problem

It occurs not only the last day of the month, what do you want on 30 May?

(I missed the 2 is error of 3, so wrote April)

• 5. Re: Date Range Calculation Problem

Yes the calc I provided is intended to select by day of the month, not by number of days in an interval. To me, this was more consistent with the concept of a "quarter".

Sent from my iPhone

• 6. Re: Date Range Calculation Problem

It's all a bit vague on the part of the client. I think I have a workaround though. I'm working on the assumption that if they select the last day of the month (any month) it will work on three full months. In other words, the first day of three months earlier. I can expand on your calculation to check if the date entered is the last day of the month and work back from there.

So yes, I will use your calculation because it works brilliantly for dates anywhere but the end of the month and I have a solution for the others. I really appreciate your assistance.

Marc

Let( d = TABLE::DateField;

If(Date(Month(d) + 1; 0; Year(d)) = d; d; OriginalCalc))

• 7. Re: Date Range Calculation Problem

You need them to clarify what they mean by a quarter. If they indicate that it's 3 months from today, use my method but subtract 3 instead of two from the month. If they say something like 90 days from today, subtract that number from your date.

Neither approach should need the adjustment you describe.

Sent from my iPhone

• 8. Re: Date Range Calculation Problem

I suspect it will probably be easier for me to explain to them that if they select the last day of the month, it will give them three calendar months. If they select a date somewhere else in the month, it will give them the three months prior to that date. I don't expect they'll have an issue with that.

Thanks, Marc

• 9. Re: Date Range Calculation Problem

Dates are complicated. People think they know what "month" and "quarter" and "year" mean, but it's actually fuzzy. You should present some of these scenarios to your client and see what they expect. Include not just the dates, but the number of days that result.

One issue I see with your proposed solution (which is pretty good) is that an end date of (in American formatting) 5/27/17, 5/28/17, 5/29/17, and 5/30/17 all give the same start date: 2/28/17. Is that acceptable?

If the monthly option allows for custom dates, you'll run into similar anomalies.

• 10. Re: Date Range Calculation Problem

Experts,

I work with MSSQL daily.  My mind set is SQL based.  My first thought here is to suggest using a date dimension table to pull the dates ranges from.  If the tables aren't ODBC but are FileMaker, then a date dimension table isn't available.  However, wouldn't it make since to import a date dimension table into your project for such reasons?

The FMP intrinsic date functions are minimal (no dateadd or datediff ?)

Ron

• 11. Re: Date Range Calculation Problem

That seems like killing an ant by dropping a mountain on it. Dates are very easy to work with and are actually jus integer values "under the hood".

The only issue that I see here is getting a clear definition from the client for what they mean by a "quarter". It should either be 90 days or three months. If they want days, subtract 90 from the end date. If they want "months", use the date function to compute a date for the same day of the month but with 3 subtracted from the month.

Sent from my iPhone

• 12. Re: Date Range Calculation Problem

I see the problem as being platform independent. It has to do with what the definition of date collections ( "month" "quarter" etc) and how they match up with client expectations.

As I understand of the term "date dimension table", those assumptions have already been baked into the table.

I don't see FM not having the functions dateadd or datediff as being a loss. FM's "way of doing things" is different, but minimal has its upside.

• 13. Re: Date Range Calculation Problem

I hear you Bruce, The saying you can't teach an old dog a new trick isn't quite accurate; an old dog can learn new tricks it just takes a bit longer.

I code in VB.NET, MS Office Suite VBA, MSSQL, SSIS, SSRS, BO data Integrator, Sagent ETL, SalesLogix, Symitar (banking SW) and others...and now in FMP15.

I cram a lot of (varying) syntax in my head...I tried flushing my cache once but the quarters stayed in the bottom of the toilet...

I will learn FMP; it's just that the more I dive into it, the deeper I find myself, the greater the need to come up for air.  FMP is intense!

Ron Harris