Solved

# Formula based on date report refreshed

Posted on 2008-06-16
Medium Priority
491 Views
I am trying to create a Recommended Reorder Report.  We previously used re-order points to determine when we needed to order goods, but now management wants this all based on estimated weeks of stock remaining.  Although I am chugging right along, I have hit a snag.  When we have new items with no sales history, I can't use the formula that is built on historical sales numbers.
We have come up with an alternative, trying to build a formula based on the date a report gets refreshed.

For example:
If the report is refreshed from March 3-8, then we would divide 26 by 1, 1 being the first week;
if the report is refreshed from March 10-15, then we would divide 26 by 2, 2 being the second week and so on...
We are using 26 weeks from March through August to build a formula here.

Now to the meat of the question:
Is there a way to build into the formula the refresh date?

Any help would be very appreciated.  If you need any further information, I will be happy to give it.
rrr.gif
rrr.gif
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 77

Expert Comment

ID: 21796725
This will give you the current week number...

//get current week number based on week 1 starting on 1st Monday in March

datetimevar firstmon;
numbervar x;
x:= dayofweek(Dateserial(year(currentdate),03,01));
if x>2 then
firstmon:= dateserial(Year(currentdate),03,10-x)
else
firstmon:= dateserial(Year(currentdate),03,2);
datediff("w",firstmon, currentdate)+1
0

LVL 101

Assisted Solution

mlmcc earned 400 total points
ID: 21796835
To get the weeks you could use a formula like
DateDiff('w',Date(2008,03,03),CurrentDate)

mlmcc
0

LVL 2

Author Comment

ID: 21802517
if (??Refresh Date??)=3/1/08 then .....
else if (??Refresh Date??)=4/1/08 then.....

Is there any way to build a formula dependent on the data refresh date?

Thanks for the help thus far.  Those formulas help out alot!

-Brian
0

LVL 77

Expert Comment

ID: 21803034
Are you saying that 'RefreshDate' is a field in your data?
If so, replace currentdate with {Table.Refershdate} in either of the solutions given so far.
0

LVL 2

Author Comment

ID: 21803452
No, refresh date isn't a field.  This is where I am running into problems.  There is no field in any table in my database that records current date or anything like that.

CurrentDate gives me date from which to build a formula?  I guess I could use this instead of any refresh dates.
0

LVL 77

Accepted Solution

peter57r earned 1600 total points
ID: 21803635
The value Currentdate is interpreted by CR as the run date of the report.
0

LVL 2

Author Closing Comment

ID: 31467716
Thanks for the help guys.  I really appreciate it!

-Brian
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
###### Suggested Courses
Course of the Month9 days, 17 hours left to enroll