Running Totals - prior month calc 1

[bv0719]

Hello...

Trying to find the right formula for running totals so I can refresh a report without needing to edit the individual running totals dates.

CR2008

The report is setup as follows (12 months):

GF2: Running Totals
#1 -
Field to summarize: 'sales'
Evaluate: Formula - {date} in Date (2008, 8, 01) to Date (2008, 8, 31)
Reset: 'customerID'

#2
Field to summarize: 'sales'
Evaluate: Formula - {date} in Date (2008, 7, 01) to Date (2008, 7, 31)
Reset: 'customerID'

and so on for 12 months. I would need to edit each date field to capture to correct months sales in each running total.

Anyway to enter a formula so that the report looks at todays date and backs into the dating for each running total?

Thanks,
Bill V

 

[kskid]

Why not use a crosstab to do your grouping. Set the row for each year and the columns for the months.

 

[lbass]

Use formulas like this:

//{@ThisMo}:
{table.date} in Dateserial (year(currentdate),month(currentdate),1) to Dateserial (year(currentdate),month(currentdate)+1,1)-1

//{@LastMo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-1,1) to Dateserial (year(currentdate),month(currentdate),1)-1

//{@TwoMosAgo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-2,1) to Dateserial (year(currentdate),month(currentdate)-1,1)-1

//etc.

-LB

 

[bv0719]

LB,

Beautiful! You're always a great help.

Thanks,
Bill V

 

[bv0719]

LB,

Sorry, I'm not getting the progression here. hat should //{@ThreeMosAgo}: look like?

I've tried:

//{@ThreeMosAgo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-3,1) to Dateserial (year(currentdate),month(currentdate)-2,1)-1

//{@TwoMosAgo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-3,1) to Dateserial (year(currentdate),month(currentdate)-2)-1

etc.

Can't seem to find the right combination. Of course, the first three formulas you provided work perfectly. Getting the syntax right on the remaining is proving troublesome.

Thanks again.

BV

 

[lbass]

//{@ThreeMosAgo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-3,1) to Dateserial (year(currentdate),month(currentdate)-2,1)-1

//{@FourMosAgo}:
{table.date} in Dateserial (year(currentdate),month(currentdate)-4,1) to Dateserial (year(currentdate),month(currentdate)-3,1)-1

Just change the monthnumbers by incrementing each by one (or since they are negative, I guess I should say decreasing each by one). This basically gives you day one of that month to one less than day of the next month--to allow you to get the correct month end date.

-LB

 

[bv0719]

LB,

Was just reading the help file on dateserial. Getting close to realizing what syntax to use. Thanks again for tha assistance.

BV

 

[lbass]

You don't have to change anything BUT the months--the dateserial will adjust the years accordingly.

-LB

 

[bv0719]

LB,

Well... That's what I was doing however the numbers weren't coming out correct. After much troubleshooting it seems the totals are not 'refreshing' right. If I filter down to one customer I can get the totals to work by using saved data. Refreshing gives the wrong total. Refresh using saved data does. Odd.

BV

 

[bv0719]

LB,

Ah Ha... I had a supression formula that was looking at nulls. This was skewing the results. Looks better now.

Thanks again,

BV