Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: HELP! User-defined Quarters

    I am running into a problem with defining Quarters based on start of contract with a customer. The following code in a calculated field in the query does the job - almost.

    What I seek is for the report to group monthly sales into "customer quarters" (not calendar quarters) and provide quarter summaries. Calendar quarters are fixed (ie, Q1=Jan/Feb/March, Q2=April/May/June, etc). My customer quarters would key off of the contract date. For example, if a contract started in May, its quarters would be of the form: Q1: May/June/July, Q2: Aug/Sep/Oct, etc.

    The problem I am having is in the report - it does not appear to recognize year-end months; if the contract starts in November, Q1 should be Nov/Dec/Jan, with the first two months coming from the prior year and Jan coming from the next year. My report groups Jan from the beginning of the first year, Nov and Dec, as Q1.

    Here's the code:

    ContractQuarter: IIf(Month([SaleMonth])-Month([StartDate])<0,IIf(Month([SaleMonth])-Month([StartDate])>=-3,Year([SaleMonth]) & "-" & "Q4",IIf(Month([SaleMonth])-Month([StartDate])>=-6,Year([SaleMonth]) & "-" & "Q3",IIf(Month([SaleMonth])-Month([StartDate])>=-9,Year([SaleMonth]) & "-" & "Q2",IIf(Month([SaleMonth])-Month([StartDate])>=-12,Year([SaleMonth]) & "-" & "Q1","NULL")))),IIf(Month([SaleMonth])-Month([StartDate])<3,Year([SaleMonth]) & "-" & "Q1",IIf(Month([SaleMonth])-Month([StartDate])<6,Year([SaleMonth]) & "-" & "Q2",IIf(Month([SaleMonth])-Month([StartDate])<9,Year([SaleMonth]) & "-" & "Q3",IIf(Month([SaleMonth])-Month([StartDate])<12,Year([SaleMonth]) & "-" & "Q4","NULL")))))

    Thanks for the help.

    J.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    By far to complex....

    Try using something much simpler, like using datediff... eg.
    ?datediff("q",dateserial(2004,4,1),date())
    0
    ?datediff("q",dateserial(2004,3,1),date())
    1
    ?datediff("q",dateserial(2003,3,1),date())
    5


    Regards

    The Mailman

  3. #3
    Join Date
    Mar 2004
    Posts
    82
    Hi,

    Could you explain?
    I cannot put in "fixed" date years. I pull up an error when I tried something like this:

    DateDiff(q,DateSerial(Year([SaleMonth]),Month([SaleMonth]),1),[StartDate])

    where: SaleMonth is the month of sale; StartDate is the start date of the contract with a customer, and the "1" in the DateSerial argument represents the first of the month.

    When I ran it, I got a parameter box asking me to specify "q".

Posting Permissions

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