Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    15

    Unanswered: Rolling 12 Month Report

    Thanks in advance to anyone who can assist!

    I need to create a rolling twelve month report that shows a win/loss ratio of quotes to jobs. For example:

    March 2004 = Sum(Quotes won between March 2003 and March 2004)
    April 2004 = Sum(Quotes won between April 2003 and April 2004)

    If you can get me this far, I'll figure out the rest. Is this possible to do in a query, or is code required? I've read similar posts suggesting cross-tab queries with partition functions, but that doesn't quite achieve what I'm going for here.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hard coded:
    Code:
    SUM(Iif([QuoteSubmittedDate] Between #2004/03/31# AND #2003/03/01# And [QuoteWon] = True, 1, 0))/SUM(Iif([QuoteSubmittedDate] Between #2004/03/31# AND #2003/03/01#, 1, 0)) AS March_2004_QuotesRatio
    You could get more complex with some groupings and use of DATEADD and stuff of course too. Maybe even incorporate into a crosstab.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    AirCode:

    Code:
    SELECT 
    MonthName(Month(QuoteDate)) & "_" & CStr(Year(QuoteDate)) AS QuoteTime, 
    
    SUM(Iif([QuoteWon] = True, 1, 0))/Count([QuoteWon]) AS QuoteRatio
    WHERE QuoteDate >= CDATE("01/" & CSTR(MONTH(QuoteDate)) & "/" & CSTR(Year(QuoteDate) - 1)) GROUP BY MonthName(Month(QuoteDate)) & "_" & CStr(Year(QuoteDate)), Month(QuoteDate), Year(QuoteDate) ORDER BY Month(QuoteDate), Year(QuoteDate)
    BTW - anyone know why code tags are sometimes ample size and sometimes teeny with scroll bars? Appears to happen when I write directly rathrer than paste....
    Last edited by pootle flump; 01-13-06 at 12:00. Reason: Funny code window thing again....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    How about something like this
    Code:
    SELECT dtYYMM, Sum(lngValue) AS SumOflngValue
    FROM tblData, (SELECT DISTINCT Format(dtDate,"yyyy-mm") AS dtYYMM FROM tblData) AS tblYearMonth
    WHERE (DateDiff("m",DateSerial(Year([dtYYMM]),Month([dtYYMM]),1),[dtDate]) Between -11 And 0)
    GROUP BY dtYYMM;
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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