I have tried a few different formulas in Crystal Reports for retrieving sales analysis data for certain time frames and cannot get any of them to come up with the correct calculation that I need. If anyone can help me it would be much appreciated.

I need three separate calculations …

10 Week Sales (back from today)
1 Week Sales (back from today)
3 Weeks Sales – (Precious year from today ahead 3 weeks)

Here are two of the formulas I have tried for the previous year’s sales – they both return the exact same total - ALL of the quantity ever sold instead of just the total sold within the date range.

IF {SR65A Transactions.Date} = (CURRENTDATE -365) TO_ (CURRENTDATE -344) THEN
sum({SR65A Transactions.Quantity sold}) ELSE
0

On this one I created a parameter to ask for the date range.

IF {SR65A Transactions.Date} = {?3 Week Prior Year} THEN
sum({SR65A Transactions.Quantity sold}) ELSE
0

I am obviously way off track here or completely missing something.
Please help…

Hi Reen

I would create two parameter fields, one for the StartDate and one for the EndDate, each asking for a discrete value, and then use a formula like this:

IF {SR65A Transactions.Date} >= {?StartDate} and {SR65A Transactions.Date} <= {?EndDate}
THEN {SR65A Transactions.Quantity sold}
ELSE 0

Not only does this offer maximum flexibility (although it is a bit user unfriendly) but it also allows you to put the two dates on the report header, such as From {StartDate} To {EndDate}

Notice I took out the sum(..) text in your formula. You don't want that if you put the field in the Detail section, and then simply insert a subtotal on that field.

Steve

Hi Reen,

Steve's comment regarding removing the SUM in the formula is right on. That will likely solve the question of getting the same total for all formulas. Also, using the parameters for selecting dates is great and flexible if you want the report to be based on a single date range of sales. But it could be cumbersome if you want multiple columns in the report, each based on a different range.
---
I should mention that if you are using Adagio SalesAnalysis, you will want to pick a different report table. You have one named SR65A, which is based on the old DOS-based Sales Analysis. You should be using the SRW68 table, which has all the information stored in Adagio Sales Analysis (and not necesarily in the old DOS version of the program)
---
When it is desired to have multiple columns represent different date ranges, I like to create a formula that calculates the number of days difference between today and the transaction date:
Formula 'Date diff': CurrentDate - {SRW68 Transactions.Date}

This gives an integer result that is the number of days old for the invoice. Future dated invoices will have a negative number.

Then it is easier to determine the date ranges needed:
10Week:
IF {@Days diff} IN 0 TO 69
THEN {SRW68 Transactions.Quantity sold}
ELSE 0

1Week:
IF {@Days diff} IN 0 TO 6
THEN {SRW68 Transactions.Quantity sold}
ELSE 0

LY3Weeks:
IF {@Days diff} IN 344 TO 365
THEN {SRW68 Transactions.Quantity sold}
ELSE 0

And so on, whatever numbers are required. There also are pre-defined Date Ranges available in the formula editor for comonly used ranges:

IF {@Days diff} IN YearToDate
THEN {SRW68 Transactions.Quantity sold}
ELSE 0

OK Thank you. I will see if I can now get this thing to work. I might be back for more help.

Thanks again.

Good afternoon ...

Once again I need some help on this if possible...

I need to know which fields I can link between the following:
ICW65 Items and SRW68 Transactions so that the sales analysis comes in for each item???

I also have the following tables in the report ...
ICW65 Items Optional Fields
ICW65 Items Vendor Information
ICW65 Report Groups

Thanks

Reen:

Not knowing the report my thinking is that you want to start with Sales Analysis and link in the IC tables. Linking the item number in SA to IC Items then allows you to link from IC Items to the other IC tables.

If you are looking for one line per item then summarize the data and report only the group footer.

I am having trouble with summarizing the data. I cannot seem to get this set correctly to sum all the transactions in each time frame (1 wk, 10wk, 3wks prev.) It is only dumping in the quantity of the very last transaction in each period.

Have you placed the SalesAnalysis records in the detail section of the report, inserted totals and then suppressed (hidden) the detail section?

Thank you Andrew. I did not have it set up that way. Now that I do it is working perfectly.

Now that I have the Sales Analysis working and giving me the correct numbers, it is only showing the very last item listed in each group. I have it set up as follows...

Page Header - Report Name

Group Header #1 - Group Name and all column headers

Details - Weekly sales formulas which I need to run totals on - supressed

Group Footer #1 - Item No, Description, UOM, QonH, QonSO, Base$, etc. AND the Sales Forecast (which is a formula calculated off the Weekly sales formulas and their sums)

Page Footer - Date/time/page no.

Can anyone tell me what I am doing wrong or missing to get all of my items to show in each group?