Compare Dates

[LuisC]

I have a sales table and I want to display sales data from the current date and for the same date last year. So if it 12/01/2007 I wnat to see sales form today and for 12/02/2006 on the form. I have the form with todays sales and 2 tables. One has current sales and the other previous years sales.

[Lee Smith]

Comparing the same date a year apart could be like comparing apples to oranges. There are a lot of factors that can have an effect on how the sales went.

Day of the Month (1st, 15, 30th, or a local major employer's payday like every Friday)

Day of the Week (Monday, Friday, Saturday, ...)

Season (Xmas, Easter, Back to School, etc.)

Beyond that, how is the economy doing, it is it up, down, or about the same.

For the sake of just seeing one year to the next for the same date, I would use a Relationship based on the date, and view it all in a Portal.

Lee

LuisC.fp7.zip

Edited January 4, 2008 by Guest

[LuisC]

Lee,

Thanks for the quick reply. This does work but I need to see Thursday January 3rd 2008 compared to Thursday January 4th 2007. We use this to look at same day sales for our restaurnats and it gives the managers an idea of what to expect in sales.

Fortunatley for us the economy is doing pretty good here for now but you never know,

Thanks,

Luis

[Lee Smith]

I modified my file to show how this might work.

LuisC2.fp7.zip

[LaRetta]

Comparing weeks usually works well. But it seems you might want what a Sales Manager (who was regional sales manager for Nabisco for 25 years) wanted, ie, a daily comparison. I will ask you what I asked him ...

Which is more important, a day of week comparion (what did THURSDAYS look like a year ago) or a DAY of month (what did the 4th look like a year ago)? Are you more interested in the closest Thursday one year in the past? Or the day in question? It matters in the calculation used. There is also the question of leap year - which rule applies? For example, 2/29/2004 is Sunday. The next year, there is NO 29th! And Sunday ends on the 27th! If you want the same day of year only subtracting 1 year then it is simple. But keep in mind that, because months are unequal, it will never be the same DAY as the current day.

If Lee's example of weeks won't work for you, then provide the rules and we can provide specifics. To get you rolling, to look one year back (regardless of day of week or month )

Date ( Month ( date ) ; Day ( date ) ; Year ( date ) - 1 )

If you want more complex 'specific dates' then you'll need to give us the rules. Another example of the 'rule break' would be: What would you want to do it you want 'the closest Thursday' and it's 2/28/2008 Thursday. It would be either 2/22/2007 or 3/1/2007. We need rules. :wink2:

[comment]

I agree that it could be better defined, but for all practical purposes basing the relationship on date - 364 seems close enough to me. I'd stay away from WeekOfYear() - see the first week of 2005 as an example why.

Any method you choose, there's not going be a one-to-one match between years (for example, in my method Dec 31, 2008 would be compared to Jan 2 of the SAME year). That's another reason why all sales data should be in the same table to begin with - the primary reason being that a year is not an entity.

[LuisC]

Lee,

Again thanks for the quick response. Your solution worked perfectly.

[LuisC]

LaRetta,

Lee's solution did work but leap year is a concern. I am comparing 2 tables, one is in an SQL db through ODBC and the other is a ststic table with only last years sales. It works but is this the bet way to handle this?

Thanks for the help,

Luis

[LaRetta]

Hi Luis,

Are all Sales in the same table? What are the rules you wish to apply? We can't suggest whether you are using the best approach when we don't know your specific needs. What are you impressions of what I said and what Comment said about it? We need information.

[Søren Dyhr]

Comparing the same date a year apart could be like comparing apples to oranges

Beware of greeks bearing gifts:

http://www.briandunning.com/cf/799

But if you read carefully, will you notice that our endevours have begun to make some sense at least, he now speaks of:

Estimates the employment time between

Our effords havn't been in vain - phew!

--sd

[LaRetta]

Yeah, Soren, I'd seen that and I laughed for two solid days. :wink2:

[LuisC]

The sales are in one table but I created another table with previous years sales. It seems to work but will this limit me?

Thanks,

Luis

[LaRetta]

It seems to work but will this limit me?

The question is when you'll be limited, Luis, not if. A good question to always ask yourself is, 'Does the data from the two years have the same fields in common?' If so then it should reside within the same table. If the fields are different then it should be split. A date is not a field but data itself; no need to split in that case.

The first time the Sales Manager requests an 18-month report you'll be glad you left your sales in one table

LaRetta