Solved

Formula based on date report refreshed

Posted on 2008-06-16
7
488 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
[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
  • Learn & ask questions
  • 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 101

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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 …
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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