Need report to show last year sales and this year and the variance

[brendasql]

Hi,
I'm trying to create a report that will be display as a crosstab. I would want the customers as my rowheaders and my column headers to be LY/MTD, TY/MTD, and var% (this being (TY/MTD - LY/MTD)/ LY/MTD) group by month. I have tried writing a command to give me the qty for 2005 and 2006, but the results are coming up 2005 then 2006. I welcome any advice.
Thanks
B

 

[synapsevampire]

You can do this manually using 3 formulas:

// grouped by month, in the group header:
whileprintingrecords;
numbervar MonthTotYr1:=0;
numbervar MonthTotYr2:=0;

//Details
whileprintingrecords;
numbervar MonthTotYr1;
numbervar MonthTotYr2;
If year({table.date}) = year(currentdate)-1 then
MonthTotYr1:=MonthTotYr1+{table.value}
else
MonthTotYr2:=MonthTotYr1+{table.value}

Group Footer formulas:
whileprintingrecords;
numbervar MonthTotYr1;

whileprintingrecords;
numbervar MonthTotYr2;

whileprintingrecords;
numbervar MonthTotYr1;
numbervar MonthTotYr2;
(MonthTotYr2 - MonthTotYr1)/ MonthTotYr1

Suppress the details and group header.

-k

 

[brendasql]

Are you saying to not use the crosstab?????

 

[brendasql]

I have tried the formulas and the results are 0. I would like for the report to be display in the following fashion.
January
LY/MTD TY/MTD
QTY Sales QTY Sales % QTY %Sales
Customer

I could really use the help. I am new to Crystal, but I know that this can be done. So far I can get Jan 05 Qty and Sales By Customer then I after Dec 05 I can see Jan 06. But surely there is a way to group the Months together then the years....... If this can not be done in Crystal, will someone please let me know.

 

[ianoctdec]

Hi brendasql,

I think you need to write formulas for each month (for TY and LY), starting Jan till running month:

@TYJanSales
if month(table.SalesDate) = 1 then
table.SalesDate
else
0
and so on...
Use these formulas as sumary fields in the crosstab.

Daniela

 

[synapsevampire]

You don't need seperate formulas for each month for each year...

I explained how to do this, and then you respond with it doesn't work, rather than showing what you tried.

Please post what you tried if you want further assistance.

-k

 

[wavecruzin]

I'm trying to do something similar. I have the following fields I'm trying to chart:

Columns: Commodity
Rows: CurYear, PrevYr
Summary: CurYearAmt, PrevYrAmt

I want it to look like this:

A B C D
2006 1 4 3 2
2005 2 3 2 4

When I add two rows and two fields to summarize it's not working.

 

[brendasql]

Hey wavecruzin! Give this thread a try.

http://www.tek-tips.com/viewthread.cfm?qid=1269633&page=1

The steps that are explained by lbass are like the ones that I used. Hope it helps.
Brenda