Solved

Formula based on date report refreshed

Posted on 2008-06-16
7
483 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Next Full Week 4 61
C# Crystal Reports 15 62
C# Infragistics UltraGrid - Export to Excel 2 52
Formatting a Crystal Report 2 37
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…
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now