comparing sales this year to last year

[AccUser]

Using Crystal Reporting 9.0.

I am accessing table via SQL. Table contains the actual quantity sold. How do I create a report that will give me the following:

Product / Current Year Sold This Month / Current Year Sold YTD / Previous Year Sold This Month / Previous Year Sold To Date / Total Dollar Sales / Difference Between Year To Date Sold This Year Minus Last Year.

Datefield: How can I query for two different dates?

 

[lbass]

First, create a record selection formula that includes dates for the previous year and the current year. This might be:

year({table.date}) in year(currentdate)-1 to year(currentdate)

If you have no need of information for the previous year after the current month in the previous year, e.g., if you don't need info for 4/03 to 12/03, then you could use:

{table.date} in yeartodate or
{table.date} in date(year(minimum(yeartodate))-1, 01, 01) to date(year(maximum(yeartodate))-1, month(maximum(yeartodate))+1,01)-1

Then group on Product, and then create a series of formulas to be placed in the detail section:

//{@currmo}:
if {table.date} in monthtodate then {table.amt}

//{@currmoprevyr}:
if {table.date} in date(year(minimum(monthtodate))-1, month(minimum(monthtodate)), 01) to date(year(minimum(monthtodate))-1, month(minimum(monthtodate))+1, 01)-1 then
{table.amt}

//{@curryrtodate}:
if {table.date} in yeartodate then {table.amt}

//{@prevyrtodate}:
if {table.date} in Date(year(minimum(yeartodate))-1, 01, 01) to Date(year(maximum(yeartodate))-1, month(maximum(yeartodate))+1, 01)-1 then {table.amt}

//{@diffcurryrprevyrtodate}:
{@curryrtodate} - {@prevyrtodate}

I'm not sure what time period you meant for "Total Dollars," so you would have to define that for further help.

You would insert summaries on the above formulas at the group level and then suppress the details.

-LB

 

[AccUser]

How would that play out when the date is not really a date field, but a string field, specifically, date field is YYYYMM (200301, 200302, etc)?

 

[AccUser]

Total Dollars = a field in the table that contains the dollar amount of the quantity sold, found in another field.

 

[lbass]

If you have a {table.qty} field and a {table.dollarssold} field, you can substitute them in the above formulas as needed, but you still haven't explained what time period you want "Total Dollars" for.

You could convert the string field to a date field. One method is:

date(val(left({table.stringdate},4)),val(right({table.stringdate},2)), 01)

-LB

 

[AccUser]

Sorry about the Dollar Field.

If I sold 10 Oranges last for 5 cents, and I sold 20 Oranges this year for 10 cents, my Total Dollars Sales for last year would be 50 cents, but my Total Dollar Sales for this year would be 2 dollars.

The detail records in the table would include the quantity and the dollar sale or value amount, regardless of the date sold.

 

[lbass]

If you are saying you want to display Total Dollars for each of last year and the current year, then you would need to use a record selection statement that brings in all of the dates from the previous year and the current year (see my first suggestion above). Then create a formula:

//{@TotalDollarsLastYr}:

if year({table.date}) = year(currentdate)-1 then {table.dollarssold}

For the current year, use:

//{@TotalDollarsCurrYr}:
if {table.date} in YearToDate then {table.dollarssold}

Insert summaries on these.

-LB

 

[AccUser]

thanks..I'll try it out...I do have to apologize. I am quite new to CR-9. I was used to creating queries using MSAccess through the GUI. Not to adept to SQL yet, although I hear it is quite easy to learn and most powerful.

 

[AccUser]

Sorry for taking so long to get back to you. I thought it prudent that I take at least an intro to CR-9 course (since I am a newbie!).

Anyway, I was able to create the formulas (I was actually naming the formulas with the "//" - doh!) and get some test data. Problems?

Yes...The formulas are giving information up to and including the current month this year and the current month last year. I would only need information for ytd last year, but not including that current month. Same for this year.

I may not be explaining myself correctly, but think of this way:

If the current month/year is April 2004, I need sales info for: March 2003 / Jan-Mar 2003 / March 2004 / Jan-Mar 2004.

The reason is because since MonthEnd is done the current month for the previous month, the current month's sales will always be zero! But the yeartodate will always reflect sales up to and including the previous month.

Does that explain my situation?

 

[lbass]

I think you could use:

//{@lastfullmonth}:
if {@date} in lastfullmonth then {table.amt}

//{@yeartolastfullmonth}:
if {@date} in date(year(minimum(lastfullmonth)), 01, 01) to
date(year(minimum(lastfullmonth)), month(minimum(lastfullmonth))+1, 01)-1 then {table.amt}

//{@monthlastyear}:
if {@date} in date(year(minimum(lastfullmonth))-1, month(minimum(lastfullmonth)), 01) to
date(year(minimum(lastfullmonth))-1, month(minimum(lastfullmonth))+1, 01)-1 then {table.amt}

//{@lastyeartolastfullmonth}:
if {@date} in date(year(minimum(lastfullmonth))-1, 01, 01) to
date(year(minimum(lastfullmonth))-1, month(minimum(lastfullmonth))+1, 01)-1 then {table.amt}

-LB

 

[AccUser]

It's a bootifull ting!

 

[AccUser]

Well, it's seems I made a mess of things!

Mgmt wants this report, but not as two separate reports, but rather as one. I will only include the fields that I need to do calcs on.

Part YTD Sales YTD Forecast Variance

100 345.000 100.000 245.000
200 0.000 50.000 - 50.000
300 400.000 0.000 400.000

Problem is that Sales data is located in 1 table and Forecast data is located in another table.

Any attempt to link tables results in failure (no data on report and report, like Energizer Bunny, keeps going and going ang going.

I contacted CR Paid Support and they recommended MainReport for Sales data and SubReport for Forecast Data. Link is on the Partno. Day and half later, we were finally able to come up with 1 report that has part data on 1 line. Good? No. I can't do any calculations. Data from sub is not coming to main (all zeros). On top of that, just to run report takes about 15 minutes! Is this normal?

Help me out, kid.

 

[lbass]

Subreports do make reports slower. Not sure why your linking does not work. Is there a {sales.PartID} and a {forecast.PartId} field that you could link on? I would certainly try linking first. Are there multiple forecasts per PartId? If so, is this based on a monthfield, for example, that you could place a second link on?

If you go the subreport route, you can get the desired display on one line, but to do calculations between main and subreport values you have to use shared variables. You didn't explain what section you are using for the "one line", but let's say it's the group footer for part number.
You would need to insert a second group footer so that you could place the subreport in GF_a and your main report fields and calculation in GF_b, since the shared variable can only be used in a section below where the subreport is fired. In order to display the results in one line, go to the section expert and format GH_a to "Underlay following sections".

To do the calculation, you first must create the shared variable within the subreport. Create a formula {@YTD Forecast}:

whileprintingrecords;
shared numbervar YTDforecast := {Forecast.YTDForecast};

If the forecast is a summary or something other than a field, substitute the summary or formula for {Forecast.YTDForecast} in the formula. You must place this formula on the subreport--use it instead of the field itself that you want to display.

Then in the main report, create a formula {@variance}:

whileprintingrecords;
shared numbervar YTDforecast;

{Sales.YTDSales} - YTDforecast

Substitute whatever you are using to calculate YTD Sales for {Sales.YTDSales} in the above formula--it's probably a summary like: sum({@yeartolastfullmonth},{table.partno}).
Place {@variance} in GF_b. You will need to size the subreport in GF_a so that it does not overlap {@variance} when the section is underlaid.

-LB

 

[AccUser]

(lbass - sorry for delay in getting back to you)

Well, I was finally able to get the report to work...to a point.

I am using the record selector while I test to keep the output to a minimum.

When entering a part and year, such as '10000' and '200401', I get the expected results. However, using any other part results in the YTDSales being blank. Why would it work for one part and not for another?

 

[AccUser]

lbass, forgot to mention.

When I run the subreport by itself and remove all filters, I get multiple lines, each representing the different part numbers. This is what I would expect.