TY LY Sales Comparison Rpt by Dept

[jdhamot]

CR 2008 w/ SQL Database

Hello,
I would like to thank anyone who takes the time to help me out with a report I'm building. I have just recently got back into Crystal reports after 5 years. I am trying to build a report that will show sales for this year compared to last year along with % change by department. The report will look similar to this:

Candy Graphics Shoes
06/22/09 200 300 900
06/22/08 100 600 900
+/- % 100% -100% 0%

06/23/09 450 200 1000
06/23/08 300 250 1250
+/- % 50% -20% -20%

and so on for the rest of the week.

The only way that I can get this layout with my very foggy Crystal knowledge is without using any grouping and creating each individual formula. I can do this, but it seems extremely repetitive and definitely not using the power of CR.

I have tried using a pivot table with selection criteria I found while searching other similar questions to mine. Such as this:

{TB_RECEIPT.DATE_CLOSED} in {?MyDateRange} or
{TB_RECEIPT.DATE_CLOSED} in dateadd("yyyy",-1,minimum({?MyDateRange})) to dateadd("yyyy",-1,maximum({?MyDateRange}))

my results turn out with left hand row names of:
06/22/08 instead of 06/22/08
06/23/08 06/22/09
06/22/09 06/23/08
06/23/09 06/23/09
etc. etc.

I would prefer to stay away from a pivot table if possible since I have many Departments running across the top columns. If anyone has any suggestions on how to create this report other than separate formulas I would greatly appreciate it. I hope I have explained my issue clearly.

Thank you!
Josh

 

[Turkbear]

Hi,
Please post your table(s) layout...

In general,try something like : Group on Year and Department ( within the range you want - formatted to show just the Year) .
Sum the sets of detail amounts for each Department by Year and in the Report footer create formulas to compute the % difference between the Year summaries for each Department.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdhamot]

Hi,

Thanks for the quick response! The problem is that I need this by each day for a week comparing Monday of this year to the same Monday of last year, Tuesday of this year to the same Tuesday of last year, etc. If I was able to just return 2009 compared to 2008 then I could use your grouping selection. Unless I am missing something in what you suggested.

My table structure is somewhat complex since I have to sum sales by day by department. TB_Receiptline has price and quantity for each sale. I can link that table to TB_Skus that has a link to TB_Styles, which is finally linked to TB_Taxonomy to get my department. TB_Reciptline is also linked to TB_Receipt_Header so that I can pull in the closing date for my receipt. That closing date is also what I am using as a parameter in order to pick the first day of the week you want to look at. If there is an easier way to show or describe my table structure I will be happy to do so.

Thanks,
Josh

 

[lbass]

Monday of this year will not be the same date as Monday of the same week last year, so do you really want to compare same dates or the same day of the week for the corresponding week number?

-LB

 

[jdhamot]

LB,

I actually have a formula that will take into account our fiscal calendar depending on what the user enters as the beginning date for the parameter. Right now where I am using the dateadd("yyyy",-1,minimum({?MyDateRange})) to dateadd("yyyy",-1,maximum({?MyDateRange})) I will just insert the formula name. Also, I have since replaced the ?MyDateRange parameter with ?Start_Date and ?End_Date parameters to simplify my formulas. I am currently in the early stages of this report and since I have not dealt with Crystal in many years I wanted to have the layout resolved before worrying about last years correct data. I figure that if I use formulas correctly then I will be able to make one change later on and have it trickle down through the other calculations. If you feel this is not a wise approach please let me know, I value all input.

As I have stated in my initial post I am currently setting up formulas for every row and column of my report. I have these placed in the details section (suppressed) then inserting a summary for each in the report footer. I currently have no grouping.

Thank you for your time!
Josh

 

[lbass]

My comment was not about your fiscal year, but about your comparison. The same date last year will not be the same day of the week, and yet you referenced comparing the same Monday, etc. It does matter to the solution. Assuming you want to compare the same dates, you could do the following:

I would suggest inserting a crosstab that uses a formula for the Row:

totext(month({table.date}),"00")+"-"+totext(day({table.date},"00")

Then add a formula for summary1:

//{@This FY}:
if year({table.date}+<somenumber>) = {?fiscalyear} then {table.amt}

//{@Last FY}:
if year({table.date}+<somenumber>) = {?fiscalyear}-1 then {table.amt}

//{@Percent Chg}:
whilereadingrecords;
0

Add {table.department} as your column field.

In preview mode, select summary1->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar thisyr := currentfieldvalue;
false

Then select summary2->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar lastyr := currentfieldvalue;
false

Then select summary3->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar thisyr;
numbervar lastyr;
if lastyr <> 0 then
totext((thisyr-lastyr)%lastyr,1)//1 for one decimal

Go into the customize style tab within the crosstab expert->summary fields->check "show labels".

The crosstab should be placed in a report header or report footer section.

-LB

 

[jdhamot]

Thank you again for your time and response. I apologize for the confusion, but I am not referring to the fiscal year I am using our fiscal calendar. Not only do I have to take into account what the Monday of last year was I also have to make sure that it falls into the same week in my comparison. For instance, if you subtract 364 days from the current date it will almost always give you the same day of the same week of the same month last year. If it is a leap year you should be able to subtract 371 to take into account the extra day. I have created a formula called @fiscal_ly = ({?Start_Date} - 364). This will give me the date of the same Monday last year.

Monday_TY = {?Start_Date}
Monday_LY = @fiscal_ly
Tuesday_TY = dateadd("ddd",1,{?Start_Date}
Tuesday_LY = dateadd("ddd",1,@fiscal_ly
etc.

When I try to use your suggestion above I only seem to get results for one day. I would also probably have to use the days of the week for the row formula instead of dates. Here is similar to what I am getting:

06-15
TY 1909
LY 0
% 0
06-16
TY 0
LY 1964
% 0
06-17
06-18
06-19
etc. all giving 0 results for all columns.

I think this is happening because of the fiscal year confusion in the summary formulas. I will continue to work on these formulas tonight and hopefully have results tomorrow. If you know of anyway to not have to use a crosstab that would be very helpful also. I am having a very hard time fitting the crosstab effectively onto one page.

Thank you again for all your help!
Josh

 

[lbass]

First of all, you need to remove the parameter from your formulas. You should only be using that in your record selection formula. Then use the date field in your formulas instead. Let me know how you get on after you make that change.

-LB

 

[jdhamot]

I'm sorry LB, but I'm confused aren't you using the parameter fiscalyear in your summary formula?

//{@This FY}:
if year({table.date}+<somenumber>) = {?fiscalyear} then {table.amt}

I think I have some serious flaws in my report. This is my record selection criteria:

{TB_RECEIPT.DATE_CLOSED} in {?Start_Date} to dateadd("d",7,{?Start_Date}) and
{TB_RECEIPT.STORE_ID} = {?Store}
or
{TB_RECEIPT.DATE_CLOSED} in dateadd("d",-364,{?Start_Date}) to dateadd("d",-357,{?Start_Date}) and
{TB_RECEIPT.STORE_ID} = {?Store}

Do I need to change something in this first?

Thanks again and I hope I am not wasting your time by not understanding this.

Josh

 

[lbass]

Your selection formula is fine. The formulas you use in your crosstab need to reference the date fields, not the parameters.

-LB