Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Same Day Last Year (2000 SR1)

    I am charting Year over Year Daliy Sales with one major problem - the day of the week for the same date last year was not the same as this year.
    The table has [date] [sales] (and more but not relevelant). The [date] field includes every day of the month for 2002 and 2003. The first field in the query calculates the day of the month. To get the sales am using this:
    2002GA: Sum(IIf(Format([SDLY],"yyyy")="2002",[Gross Adds]))
    2003GA: Sum(IIf(Format([SDLY],"yyyy")="2003",[Gross Adds]))

    I need the 2002 column to give me the date plus 1 so the first of 2003 will be the 2nd of 2002 so I can chart the days of the week evenly.

    NOTE: I have a second table that includes many dimention on every date for the next few years including [SDLY] (same-day-last-year) that I can pull into this query but cannot seem to make that connection work for the results I am looking to achieve.

    I am not even sure if this will make complete sense except that perhaps someone out there has mastered this problem - any help would ber awesome!
    There is always a way.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Day Last Year (2000 SR1)

    To make sure I understand, you want to compare Thursday, January 1 with Thursday, January 2? Also, what are you charting...variance to prior year, actual numbers, etc.? How much of a date span will you chart...current day, one month's worth of data, a complete year, etc.?
    This will help in finding a solution.
    Cheers,
    Dashiell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Day Last Year (2000 SR1)

    You have it exactly. I am charting the actual number of sales for Thurs, Jan 1 with Thurs, Jan 2... etc.

    The source for the data is very large for each month so I have been storing one month of data for each month. There are 260 total locations, 31 days in 2002, 31 days in 2003 so the data for Dec is 16120 rows in that table. While the chart is for the "NE Area" with all locations summed together the data still comes in separate rows. I have considered altering the tables to separate 2002 and 2003 into two tables (around 97720 rows for the entire year) if that helps the process.

    I realize this is difficult to explain here, but I appreciate your trying to help. If you need more info let me know.
    There is always a way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Same Day Last Year (2000 SR1)

    It is not very clear to me what you are trying to do, but you might try the following:

    1. Create a Totals query based on your table. Group by the date field, sum on the amount field (Gross Adds?). Add a calculated column:

    PrevDate: DateAdd("d", -364, [DateField])

    where DateField is the name of the, uh, date field. Leave the Total option for this field as Group By. The -364 subtracts 52 weeks (7x52=364) from the date. Also add a calculated column

    SaleYear: Year([DateField])

    where DateField ... you get the idea. Save this query.

    2. Create a new query and add two instances of the query you just saved. Set the criteria for the Year field from the first instance to 2003. Join the instances on DateField in the first instance vs. PrevDate in the second instance. Add the date field from the first instance and the sum of the sales amount from both instances. Use this query as record source for your chart.

    If this is not what you need, please try to explain more clearly what you want to accomplish.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Day Last Year (2000 SR1)

    The only thing I have to add to Hans' solution is instead of hard coding the -364, you might want to have it pull from a table as in leap years, the -364 won't work. Also, if you could post a boiled down version of your database (<100kb) it might help explain things more clearly.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Same Day Last Year (2000 SR1)

    I think the -364 should be hard coded - it means exactly 52 weeks, so whether it is a leap year or not, you end up on the same day of the week. If you use something like DateAdd, you're likely not to end up on the same day of the week. Or else I haven't understood delucmi's question correctly.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Same Day Last Year (2000 SR1)

    Sorry Hans, you are absolutely correct. I didn't think it all the way through that 52 weeks (regardless of leap year) will always land on the same weekday. Thanks for clearing my mind.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •