Calculation between groups 1

[dscoiho]

I am using Crystal 10 with SQL Server.

Data sample below is currently what I am producing.

With this sample the 5 week Total Sales and 5 week Moving Avg are in the same grouping.
Currently being group by Quarter.
What I am looking to include on this report is on each grouping take the avg total from the next quarter and do % change on the totals.

ex.

12/30/2005 avg total subtract 03/31/2006 avg total and divide result by 03/31/2006 and show in the line with 12/30/2005.

Please let me know if you need further information.

A B C D E F G TOTAL
5 Week Total Sales as of 12/30/2005 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 03/31/2006 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 06/30/2006 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 09/30/2006 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 12/31/2006 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 03/31/2007 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 06/30/2007 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14
5 Week Total Sales as of 07/16/2007 $10 $10 $10 $10 $10 $10 $10 $70
5 Week Moving Avg $2 $2 $2 $2 $2 $2 $2 $14

 

[lbass]

Please show how you are arriving at your summaries. If they are formulas, please show the contents of the formulas. Is this a manual crosstab, or are columns A to G separate fields?

Could you change the order of the groups so that the quarters are in descending order (most recent first)?

-LB

 

[dscoiho]

This is a manual crosstab with 7 columns A-G

//@Week
DATEPART("ww",{Command.POSTING_DATE},0)

//Column A 5wk(repeated for B-G)
SELECT {@Week}
CASE 9 TO 13 : {Command.A}
CASE 21 TO 26 : {Command.A}
CASE 35 TO 39 : {Command.A}
CASE 49 TO 53 : {Command.A}
DEFAULT : 0

//Avg of Column A (Repeated for B-G)
Sum ({@A 5wk}, {Command.POSTING_DATE}, "quarterly")/5

// Total
Sum ({@A 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@B 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@C 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@D Cap 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@E 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@F 5wk}, {Command.POSTING_DATE}, "quarterly")
+Sum ({@G 5wk}, {Command.POSTING_DATE}, "quarterly")

//Avg Total
{@A 5wk Avg}
+{@B 5wk Avg}
+{@C 5wk Avg}
+{@D 5wk Avg}
+{@E 5Wk Avg}
+{@F 5wk Avg}
+{@G 5wk Avg}

Going through this report I have totals for A-G based on the @Weeks that are within the Case statement. I am group by Quarter at Posting_Date and at the end of each quarter doing the calculation for the last 5 weeks of the quarter. The average is based on each column and a total of all the columns for the 5 weeks and the Average. That part validates back to what required are needed. Now I need to get the percentage of Change from the Avg total at each quarter and subtract it from the next quarter and divide it by the next quarter.

ex.. (Qtr1 Avg Total - Qtr2 Avg Total) % Qtr2 Avg Total= %

I hope this helps explain it better. I am also pulling it through a command and sum it at each Posting date.

 

[lbass]

You didn't answer my question.

-LB

 

[dscoiho]

The order needs to be in ascending.

 

[lbass]

You might be able to accomplish this by using the following principles. You would have to actually perform the calculation in a group header section_a of the "next" date group.

whileprintingrecords;
numbervar prev;
stringvar currdate;
stringvar prevdate := currdate;
numbervar curr := <your group summary here>;
numbervar calc;

currdate := GroupName ({table.date}, "quarterly");
if prev > 0 then
calc := (prev-curr)%curr;
prev := curr;
prevdate & " " & calc & "%"

Or you could call prevdate and calc in separate formulas.

-LB