Solved

Formula based on date report refreshed

Posted on 2008-06-16
7
484 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now