YTD Running Total by Month

[mparshley]

I need to calculate a ytd running total of sales by month by customer. That is, for Jan 07, I need a total of sales for 2/1/06 through 1/31/07, for Feb 07, a total of sales for 3/1/2006 through 2/28/07, etc. Here is the layout

Apr 2007 May 2007 June 2007
Customer 1 YTD tot sales YTD tot sales YTD tot sales

Customer 2 YTD tot sales YTD tot sales YTD tot sales

Fields to work with: customer name, sale date, transaction date.

Thank you in advance.

 

[SkipVought]

Hi,

By the way, YTD usually means from the First of the current year thru the current date.

Where's your Sales Amount?

Transaction date should not enter into this criteria, should it?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mparshley]

I'm not sure what I should call it but it is the total for the prior 365 days for each month.

I should have listed net sale and not transaction date.

Sorry about that.
Martha

 

[SkipVought]

That's usually referred to as a Rolling Year.

You stated you want the headings starting in April 2007.

What if you run this query three month from now? Still April or did you intend it to be ThisMonth - 3?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mparshley]

It will probably be run at the end of each month, I guess that means thismonth.

 

[SkipVought]

So you only want ONE column of Rolling Year Sales?

So if you ran it now, you would want 2006/07/01 to 2007/06/30?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mparshley]

I guess I'm not explaining myself very well.

I want two years worth of rollingnetsales by mm/yy by customer.

mm1/yy1 mm2/yy1 mm3/yy1 etc.


Customer 1 RollTotNetSale RollTotNetSale RollTotNetSale
Customer 2 RollTotNetSale RollTotNetSale RollTotNetSale

 

[SkipVought]

Now it's TWO year's worth. When did that change? Your OP has ONE year's worth.

...STARTING with???

April 2007 ALWAYS???

or something else???

My original question still stands, "What if you run this query three month from now? Still April or did you intend it to be ThisMonth - 3?"

To which you replied, "I guess that means thismonth."

To which I replied, "So if you ran it now, you would want 2006/07/01 to 2007/06/30?"

To which you replied,

"...mm1/yy1 mm2/yy1 mm3/yy1 etc..."

So how do you define mm1 and when does [/b]etc[/b] end?

Please be CLEAR, CONCISE and COMPLETE.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[KyleS]

Attempt to clarify:

m1/y1 m2/y1 m3/y1 ... m12/y1 m1/y2 m2/y2 m3/y2 ... m12/y2

With each record being the sales totals for the previous 12 months? (by customer, of course)

and the 2 years are the two yars prior to the current month, so if run today the query would give the rolling year summs for 7/2005 - 6/2007?

Is this what you're looking for?

Kyle

 

[mparshley]

Very close. I need a formula that will sum the prior one years worth of total sales by month. I need 24 months of these totals so the actual input would be 36 months worth of data.

I don't need help with the report just the formula. I'm going to plug it into a Crystal Report.

Input: customer id, sale date, net sale. Each record represents 1 sale.

Query output: customer id, mm/yr, rolling total.

I'm sorry I've been so unclear. Last time I was told to give concrete examples. I guess these were too concrete.

 

[lespaul]

Something more concrete would be:

[tt]
TableName
customer id sale date net sale
data data data
data data data
data data data
data data data
data data data
data data data

Results from the records above:
CustomerID mm/yy Rolling Total
data data data
data data data
data data data

[/tt]
and the formula for the Rolling Total:

Folling Total = Record1.FieldName + Record3.FieldName

That way we can SEE exactly what you have and exactly what you want.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins