Comparing data

[BigglesUK]

I’m trying to get my head around a problem and ive finally drawn a blank. I’m trying to create a history report, which compares our sales orders over the last 4 weeks to see what changes have happened. On Mondays I plan to run a job to capture the current sales order numbers, items, qty and lineamount. Then I would like my crystal report to compare the pervious weeks qty and amount against this next weeks. A difference and a percent difference to flag up any large changes.

I think perhaps a cross tab might solve this for me, and doing my research I found this forum topic which I though perhaps might be able to help but unfortunately I haven’t been able to adapt it to my report

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

What I currently have is group on the sales order number, then group on the date captured. With the details suppressed I have a summary on the amount of items. I can then see if this increases/decreases. I also have a summary on qty ordered and line amount. I am struggling on how to compare the previous value to the current value.

Ive included screenshots, and help would be appreciated, thanks.

Running with Crystal Report IX and SQL 2000 Standard.

 

[lbass]

You should remove the date group and then follow the steps in the thread you mentioned.

-LB

 

[BigglesUK]

Hi LB, thanks for the reply.
From the similar forum post i mentioned above:

I think the easiest solution would be a manual crosstab. Insert a group on {table.category} and then create a series of formulas like:

You said to remove the capture date from my manual crosstab, which i have. My main field now is the SalesID which I have grouped them on.

//{@currwk}:
if {table.date} in currentdate-6 to currentdate then 1

As i dont have 4 weeks data I have captured every day and im using 4 days of data, so i have 4 formulas:

//{@Week1}:
if {KAB_CAPTURE_ONORDERSALES.CAPTUREDDATE} in currentdate then 1

//{@Week2}:
if {KAB_CAPTURE_ONORDERSALES.CAPTUREDDATE} in currentdate-1 then 1

//{@Week3}:
if {KAB_CAPTURE_ONORDERSALES.CAPTUREDDATE} in currentdate-2 then 1

//{@Week4}:
if {KAB_CAPTURE_ONORDERSALES.CAPTUREDDATE} in currentdate-3 then 1

//{@prevwk}:
if {table.date} in currentdate-13 to currentdate-7 then 1

//etc. for six formulas total

Then insert a sum on each formula, drag the groupname into the group footer, and suppress the detail section. Then create your percentage formulas like the following:

Ive inserted a sum of each of the 4 formula and put the sum in the group on SalesID. I wasnt sure what you meant about drag the groupname into the group footer. When i used insert summary, and selected the group, it automatically placed that sum their is that ok?

Suppresed the details section.

sum({@currwk},{table.category}) % sum({@prevwk},{table.category}) - 100

I believe my percentage formula would be:

sum({@Week1},{KAB_CAPTURE_ONORDERSALES.SALESID})
% sum({@Week2},{KAB_CAPTURE_ONORDERSALES.SALESID}) - 100

and then again for for Week 2 and Week 3, and then again for Week 3 and Week 4?

Then select each percentage formula->format field->border->color->background->x+2 and enter:

if currentfieldvalue > 20 then crYellow else crNoColor

background colouring worked well.

Anyway, yeah ive still got a few issues just trying to get my head around it. Am i going in the right direction?

 

[lbass]

Your formulas should be just like the previous post, as in:

//{@currwk}:
if {table.date} in currentdate-6 to currentdate then 1

A sum of this formula will count all records within the current week.

When you insert a summary (sum, in this case), the result will appear in the group footer. The groupname (sales order number) appears in the group header. If you drag the groupname from the group header to the group footer, then the groupname will be in the same line as the summaries. You can then suppress the detail and group header section.

For the summary, based on your described need for the percent of change, I would use:

(sum({@Week1},{KAB_CAPTURE_ONORDERSALES.SALESID})
- sum({@Week2},{KAB_CAPTURE_ONORDERSALES.SALESID})) %
sum({@Week2},{KAB_CAPTURE_ONORDERSALES.SALESID})

But it depends upon your logic.

-LB