Need to do a select on the same data

[yvwo1]

This one has me completely bewildered:

I need to select data by date for one month. I want the same data (but a month earlier) side by side for comparisom purposes.

I have 3 fields #cust #date #amt

So I would have

Customer Name

Oct-1-2003 25% Sep-1-2003 15%
Oct-2-2003 24% Sep-2-2003 19%
Oct-3-2003 22% Sep-3-2003 10%

Anyway to line up the data in this manner?

 

[MJRBIM]

Performance might not be great...but one method would be to use a formula that subtracts a month from your DETAIL date, then LINK to a "tiny" SUBREPORT that shows the #amt field for the linked date.

Depending on if your date is a DATE or a DATE-TIME field you would want to use one of the formulas below.

Name : {@PreviousMonthDate}
Formula : Date(Year ({Table.#date}),(Month ({Table.#date})-1) ,Day ({Table.#date}))

Name : {@PreviousMonthDateTime}
Formula : DateTime(Year ({Table.#date}),(Month ({Table.#date})-1) ,Day ({Table.#date}), 00, 00,00)

The results would look like this, the SUB-REPORT is in red.

#date #amt PreviousMonth #amt
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
01-Oct-2003 25% 01-Sep-2003 15%

02-Oct-2003 24% 02-Sep-2003 19%

03-Oct-2003 22% 03-Sep-2003 10%

 

[yvwo1]

What a great idea! I'll give it a try! Thanks.

 

[synapsevampire]

Create a formula to group by, as in:

day({table.date})

Then sort by the date

Now create formulas to display the data in the group footer (sppress the details and group header) for the first month, as in:

@Firstmonthdate
if day(previous({table.date})) = day({table.date}) then
previous({table.date})

@firstmonthCust
if day(previous({table.date})) = day({table.date}) then
previous({table.cust})

You can just use the table fields directly to display the latter month.

Should work fine.

-k