LYSales Column of Crystal Report (Last Year Sales Column)

jspc
jspc used Ask the Experts™
on
Hello,

I’m wondering if I could please get some help with my Crystal Report?
The 5th column in from the left called “LYSales” is not calculating the correct last year sales values and I am unsure why.

The sales financial period in Australia is from July to June.

I have saved the report with data.

File attached.
MatrixSalesByProduct12Months.rpt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you trying to run this as a rolling 24 month report where the last 12 months are "this year" and the 12 months before that are Last Year?

In this report are you trying run as 1 April 2014 through 30 March 2015 is this year and 1 April 2013 through 30 March 2104 is Last Year?

If so you have to use the reporting period to determine which 12 months to use since you actually have 3 Transaction years in the data.

You have data from April 2013 through March 2015
 April 2013 Through Jun 2013 is TransYear 2013
Jul 2013 Through Jun 2014 is TransYear 2014
Jul 2104 through Mar 2015 is TransYear 2015

Check the report I modified and the last year/ this year sales

I added 2 formulas to show the minimum accounting date and period in the report
Names start with zz

mlmcc
MatrixSalesByProduct12Months-Rev-1.rpt

Author

Commented:
Please see below:

Are you trying to run this as a rolling 24 month report where the last 12 months are "this year" and the 12 months before that are Last Year?
Yes that is correct.

In this report are you trying run as 1 April 2014 through 30 March 2015 is this year and 1 April 2013 through 30 March 2104 is Last Year?
Well no, the year should be July 2014 to June 2015 and July 2013 to June 2014

The financial year reporting periods are July to June.

Author

Commented:
Hello again, I have checked the revised file and the LYSales column doesn't seem to pick up the previous period of July to June.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The way you have it written you are pulling the data I mentioned.

How are the users going to specify which years to run for?

What date should be used?  Period 36 corresponds to Accounting Date March 2015
If you use the Period you will need to either accept the potential for having 3 years or you will have to calculate the starting period.  The other alternative is to limit the periods the user can select to the June months so you always get a full year.

If period 36 is supposed to be the end of the year , then there must be  a different date to show for the report

I attached a revised report that I added a Cross Tab to show the data in the report.

mlmcc
MatrixSalesByProduct12Monthsrev2.rpt

Author

Commented:
The User enters the date range via the accounting software before the report runs.

The saved data is only an example.

LY should be last 12 month blocks as opposed to LFYear (July to June end) .

Sorry in past posts I had this stated as a financial year, it isn't, it should be blocks.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What does the application pass to the report for the date range or period

mlmcc
Commented:
I found the accounting system needed to pass 'entry date' then the report works ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial