Solved

Formula based on date report refreshed

Posted on 2008-06-16
7
485 Views
Last Modified: 2012-08-14
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
Comment
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
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 100

Assisted Solution

by:mlmcc
mlmcc earned 100 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

by:allyourbasearebelongtous
ID: 21802517
What about something like...
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

Expert Comment

by:peter57r
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

by:allyourbasearebelongtous
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

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

Author Closing Comment

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

-Brian
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question