asked on

Formula based on date report refreshed

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

peter57r

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

SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

allyourbasearebelongtous

ASKER

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

peter57r

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.

allyourbasearebelongtous

ASKER

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.

ASKER CERTIFIED SOLUTION

peter57r

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

allyourbasearebelongtous

ASKER

Thanks for the help guys. I really appreciate it!

-Brian