Subtracting two group footers

  • Thread starter Thread starter mcmum01
  • Start date Start date
M

mcmum01

I have built a report that attempts to compare year over year changes in
commission. There are 2 groups in the report on "team" and then by "year". If
the 2009 total was $1000 and the 2010 total was $1500, I would like the
"team" total to show me $500 so I can compute the percentage chnage. Is that
possible?
 
You could try an expression like:
=Sum(Abs(Year([MyDateField])=Year(Date())) * [Commission]) -
Sum(Abs(Year([MyDateField])=Year(Date())-1) * [Commission])
 
This what my report looks like:

2009
Month Product
Jan 500
Feb 700

Year Total 1,200

2010
Month Product
Jan 900
Feb 1,100

Year Total 2,000

I would like to be able to subtract 2,000 from 1,200 to see the change year
over year.

I hope this makes more sense.



Duane Hookom said:
You could try an expression like:
=Sum(Abs(Year([MyDateField])=Year(Date())) * [Commission]) -
Sum(Abs(Year([MyDateField])=Year(Date())-1) * [Commission])

--
Duane Hookom
Microsoft Access MVP


mcmum01 said:
I have built a report that attempts to compare year over year changes in
commission. There are 2 groups in the report on "team" and then by "year". If
the 2009 total was $1000 and the 2010 total was $1500, I would like the
"team" total to show me $500 so I can compute the percentage chnage. Is that
possible?
Click to expand...
Click to expand...
 
You didn't provide the name of your year field or its data type.
However, use the same logic. Try an expression like:
=Sum(Abs([MyYearField]=Year(Date())) * [Product]) -
Sum(Abs([MyYearField]=Year(Date())-1) * [Product])

--
Duane Hookom
Microsoft Access MVP


mcmum01 said:
This what my report looks like:

2009
Month Product
Jan 500
Feb 700

Year Total 1,200

2010
Month Product
Jan 900
Feb 1,100

Year Total 2,000

I would like to be able to subtract 2,000 from 1,200 to see the change year
over year.

I hope this makes more sense.



Duane Hookom said:
You could try an expression like:
=Sum(Abs(Year([MyDateField])=Year(Date())) * [Commission]) -
Sum(Abs(Year([MyDateField])=Year(Date())-1) * [Commission])

--
Duane Hookom
Microsoft Access MVP


mcmum01 said:
I have built a report that attempts to compare year over year changes in
commission. There are 2 groups in the report on "team" and then by "year". If
the 2009 total was $1000 and the 2010 total was $1500, I would like the
"team" total to show me $500 so I can compute the percentage chnage. Is that
possible?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top