YTD and MTD math in a report.

[jdbit2byte]

(So I think I am going about this the right way, feel free to correct me if I
need to do my math in the query or elsewhere somehow.)

I am trying to make a YTD and MTD sales report.
This data is brought from 2 tables: tbl_Invoice tbl_Dealer
In table invoice there are all the purchases the dealers have made.
YTD MTD sales report means "What the dealers have bought from us"

I started with a listing with the dealers as the heading and each invoice
listed below them. Then I made an unbound box and had it sum up all the
invoice totals.

Now I can't seem to limit when the sum is summed. Meaning I have a total of
each dealers purchases but I can't split them up into "Last year" "This year"
"This month" "Last month"

Thanks,
JD

 

[Duane Hookom]

Are all values from all time periods in the record source of the report? If
so, you can sum an expression in a footer or header section with something
like:

Year to Date
=Sum(Abs(Year(SaleDate) = Year(Date())) * [SalesAmt])

You should be able to replace the:
Year(SaleDate) = Year(Date())
with other expressions for other time periods.

 

[jdbit2byte]

Thanks a lot I couldn't get any date math to work correctly,
but your idea worked great:

I am unable to get Month to Date working though. It keeps calculating the
total for last year's february.

Here is what I am trying:
=Sum(Abs(Month([Invoice_Date_List1])=Month(Date()))*[Total_Merchandise])

Appreciate the help,
JD

Are all values from all time periods in the record source of the report? If
so, you can sum an expression in a footer or header section with something
like:

Year to Date
=Sum(Abs(Year(SaleDate) = Year(Date())) * [SalesAmt])

You should be able to replace the:
Year(SaleDate) = Year(Date())
with other expressions for other time periods.

--
Duane Hookom
Microsoft Access MVP

(So I think I am going about this the right way, feel free to correct me if I
need to do my math in the query or elsewhere somehow.)

I am trying to make a YTD and MTD sales report.
This data is brought from 2 tables: tbl_Invoice tbl_Dealer
In table invoice there are all the purchases the dealers have made.
YTD MTD sales report means "What the dealers have bought from us"

I started with a listing with the dealers as the heading and each invoice
listed below them. Then I made an unbound box and had it sum up all the
invoice totals.

Now I can't seem to limit when the sum is summed. Meaning I have a total of
each dealers purchases but I can't split them up into "Last year" "This year"
"This month" "Last month"

Thanks,
JD

 

[Duane Hookom]

Your expression was looking at the month only. You need to grab the month and
year like:

=Sum(Abs(Format([Invoice_Date_List1],"YYYYMM")=Format(Date(),"yyyymm"))*[Total_Merchandise])
--
Duane Hookom
Microsoft Access MVP

Thanks a lot I couldn't get any date math to work correctly,
but your idea worked great:

I am unable to get Month to Date working though. It keeps calculating the
total for last year's february.

Here is what I am trying:
=Sum(Abs(Month([Invoice_Date_List1])=Month(Date()))*[Total_Merchandise])

Appreciate the help,
JD

Are all values from all time periods in the record source of the report? If
so, you can sum an expression in a footer or header section with something
like:

Year to Date
=Sum(Abs(Year(SaleDate) = Year(Date())) * [SalesAmt])

You should be able to replace the:
Year(SaleDate) = Year(Date())
with other expressions for other time periods.

--
Duane Hookom
Microsoft Access MVP

(So I think I am going about this the right way, feel free to correct me if I
need to do my math in the query or elsewhere somehow.)

I am trying to make a YTD and MTD sales report.
This data is brought from 2 tables: tbl_Invoice tbl_Dealer
In table invoice there are all the purchases the dealers have made.
YTD MTD sales report means "What the dealers have bought from us"

I started with a listing with the dealers as the heading and each invoice
listed below them. Then I made an unbound box and had it sum up all the
invoice totals.

Now I can't seem to limit when the sum is summed. Meaning I have a total of
each dealers purchases but I can't split them up into "Last year" "This year"
"This month" "Last month"

Thanks,
JD

 

[jdbit2byte]

Thanks for the quick reply and extra help, this works great.

Your expression was looking at the month only. You need to grab the month and
year like:

=Sum(Abs(Format([Invoice_Date_List1],"YYYYMM")=Format(Date(),"yyyymm"))*[Total_Merchandise])
--
Duane Hookom
Microsoft Access MVP

Thanks a lot I couldn't get any date math to work correctly,
but your idea worked great:

I am unable to get Month to Date working though. It keeps calculating the
total for last year's february.

Here is what I am trying:
=Sum(Abs(Month([Invoice_Date_List1])=Month(Date()))*[Total_Merchandise])

Appreciate the help,
JD

Are all values from all time periods in the record source of the report? If
so, you can sum an expression in a footer or header section with something
like:

Year to Date
=Sum(Abs(Year(SaleDate) = Year(Date())) * [SalesAmt])

You should be able to replace the:
Year(SaleDate) = Year(Date())
with other expressions for other time periods.

--
Duane Hookom
Microsoft Access MVP


:

(So I think I am going about this the right way, feel free to correct me if I
need to do my math in the query or elsewhere somehow.)

I am trying to make a YTD and MTD sales report.
This data is brought from 2 tables: tbl_Invoice tbl_Dealer
In table invoice there are all the purchases the dealers have made.
YTD MTD sales report means "What the dealers have bought from us"

I started with a listing with the dealers as the heading and each invoice
listed below them. Then I made an unbound box and had it sum up all the
invoice totals.

Now I can't seem to limit when the sum is summed. Meaning I have a total of
each dealers purchases but I can't split them up into "Last year" "This year"
"This month" "Last month"

Thanks,
JD