Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Location
    OKC, OK
    Posts
    7

    Unanswered: How to obtain previous year data in an array

    I am wanting to create a report that will look at the last 3 months of sales. The data is held in an array.
    I want the end user to be able to select the 1st month and the year for that starting month.

    My problem is how do I deal with a month selection of either January or February as the previous month(s) will be in a prior year.
    I currently have the two formulas below which work well as long as January or February are not selected.

    Month1 =
    ((local stringvar array x;
    x:=split({oe_hist_prod.units-shipped}, ";");
    tonumber(x[{?Month 1}]);) -
    (local stringvar array x;
    x:=split({oe_hist_prod.units-returned-nrstkd}, ";");
    tonumber(x[{?Month 1}]);) -
    (local stringvar array x;
    x:=split({oe_hist_prod.units-returned-rstkd}, ";");
    tonumber(x[{?Month 1}]);))

    Month2 =
    ((local stringvar array x;
    x:=split({oe_hist_prod.units-shipped}, ";");
    tonumber(x[{?Month 1}-1]);) -
    (local stringvar array x;
    x:=split({oe_hist_prod.units-returned-nrstkd}, ";");
    tonumber(x[{?Month 1}-1]);) -
    (local stringvar array x;
    x:=split({oe_hist_prod.units-returned-rstkd}, ";");
    tonumber(x[{?Month 1}-1]);))

    Month3 is similar @ x[{?Month 1}-2]

    I am unsure how to incorporate the month & year selection into either the formulas directly or indirectly through other formulas.

    Thanks for any help that can be offered.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is why we use proper dates.
    I would give the user 3 parameters:
    • Year
    • Month
    • Number of Months (Period)

    Then I would probably create a formula for StartDate:
    Code:
    Date({?Year}, {?Month}, 1) // first of the month
    Then use this in the record selection criteria:
    Code:
    {something.date_field} >= {@StartDate}
    AND
    {something.date_field} <  DateAdd(mm, {?NumberOfMonths}, {@StartDate})
    Simples
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Location
    OKC, OK
    Posts
    7
    Thanks for the help George,
    but I don't think that your formula will work or at least I don't see how it will
    You indicate that {something.date_field} would be a date field, but the data I am looking at are in two different fields, neither indicating date
    Year - a number field
    Month - a section within the array, ie a bucket for each month.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    See my first bit of code above - this takes a Year and Month and gives you the first of the month as a date to play with!
    George
    Home | Blog

Posting Permissions

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