MTD & YTD Sales

Yahoo Archive
1
You should get a consulting fee for this kind of help, Jim. Wow!
Egroups to the rescue!

Troy Funte
Liberty Electronics
----- Original Message -----
From: Jim Stetter
To: vantage@yahoogroups.com
Sent: Friday, March 16, 2001 3:17 PM
Subject: RE: [Vantage] MTD & YTD Sales


This is how I would attack this report assuming you are on monthly
accounting periods:

1. Include a date range in the filter: InvcDtl.InvoiceDate IN-RANGE
starting date, ending date

2. Create an aggregate for the "maximum value" of "Invcdtl.InvoiceDate",
"no reset", "every composite", "pre-pass" - call it "LastDate". This will
yield the highest actual date found in the filter range. Click the options
button on the aggregate definition window to see the "pre-pass" and "every
composite" parameters.

3. Create a calculated field, "MtdYrMon", --> Year(LastDate) +
(Month(LastDate) / 100) this will yield values like 2001.01 for Jan'01;
2001.02 for Feb'01 ....

4. Create a calculated field, "InvdYrMon" --> Year(InvcDtl.InvoiceDate) +
Month(InvcDtl.InvoiceDate) / 100). Again will yield values like 3 above
(2001.03 )

5. Create a calculated field "InvAmtMTD" --> iif(MtdYrMon = InvdYrMon,
InvcDtl.ExtPrice, 0). This will capture the value of an invoice line when
the Year.month of the invoice matches the Year.month of the last date in
filter range.

Note: you could do steps 3,4,5 as one complex calculation, but I prefer the
less complicated way.

6. Create an Aggregate that totals the MTD sales: Total, InvAmtMTD,
grouping field (Part, Customer... or no reset for grand total), every
composite, final pass.

7. Create an Aggregate that totals the YTD sales: Total, InvcDtl.ExtPrice,
grouping field or no reset, final pass.

Hope this helps

Jim Stetter
Snow Filtration

-----Original Message-----
From: rlunsf@... [mailto:rlunsf@...]
Sent: Thursday, March 15, 2001 10:19 AM
To: vantage@yahoogroups.com
Subject: [Vantage] MTD & YTD Sales


I am trying to build a report that prints month to date and year to
date sales, on the same report. How do I get around the filter? If
I filter by the year, I can't get MTD sales and vice-versa. Any
ideas?




[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor

Click Here to Find Software Faster


To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please go to: http://groups.yahoo.com/group/vantage/files/. Note: You must have already linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
2
I am trying to build a report that prints month to date and year to
date sales, on the same report. How do I get around the filter? If
I filter by the year, I can't get MTD sales and vice-versa. Any
ideas?
3
Try creating calculated YEAR fields and calculated MONTH fields. RB has the
formulas to do this. The create a new YEAR group and a month GROUP then
sort by year then by month then by whatever. Add the group footers for the
year and month groups and summarize totals from there.

Shirley Graver
(End User)
Sys Admin
Rubber associates Inc.
Cleveland/Akron



[Non-text portions of this message have been removed]
4
This is how I would attack this report assuming you are on monthly
accounting periods:

1. Include a date range in the filter: InvcDtl.InvoiceDate IN-RANGE
starting date, ending date

2. Create an aggregate for the "maximum value" of "Invcdtl.InvoiceDate",
"no reset", "every composite", "pre-pass" - call it "LastDate". This will
yield the highest actual date found in the filter range. Click the options
button on the aggregate definition window to see the "pre-pass" and "every
composite" parameters.

3. Create a calculated field, "MtdYrMon", --> Year(LastDate) +
(Month(LastDate) / 100) this will yield values like 2001.01 for Jan'01;
2001.02 for Feb'01 ....

4. Create a calculated field, "InvdYrMon" --> Year(InvcDtl.InvoiceDate) +
Month(InvcDtl.InvoiceDate) / 100). Again will yield values like 3 above
(2001.03 )

5. Create a calculated field "InvAmtMTD" --> iif(MtdYrMon = InvdYrMon,
InvcDtl.ExtPrice, 0). This will capture the value of an invoice line when
the Year.month of the invoice matches the Year.month of the last date in
filter range.

Note: you could do steps 3,4,5 as one complex calculation, but I prefer the
less complicated way.

6. Create an Aggregate that totals the MTD sales: Total, InvAmtMTD,
grouping field (Part, Customer... or no reset for grand total), every
composite, final pass.

7. Create an Aggregate that totals the YTD sales: Total, InvcDtl.ExtPrice,
grouping field or no reset, final pass.

Hope this helps

Jim Stetter
Snow Filtration

-----Original Message-----
From: rlunsf@... [mailto:rlunsf@...]
Sent: Thursday, March 15, 2001 10:19 AM
To: vantage@yahoogroups.com
Subject: [Vantage] MTD & YTD Sales


I am trying to build a report that prints month to date and year to
date sales, on the same report. How do I get around the filter? If
I filter by the year, I can't get MTD sales and vice-versa. Any
ideas?




[Non-text portions of this message have been removed]