allyourbasearebelongtous

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

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

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**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

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

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.

If so, replace currentdate with {Table.Refershdate} in either of the solutions given so far.

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.

CurrentDate gives me date from which to build a formula? I guess I could use this instead of any refresh dates.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks for the help guys. I really appreciate it!

-Brian

-Brian

//get current week number based on week 1 starting on 1st Monday in March

datetimevar firstmon;

numbervar x;

x:= dayofweek(Dateserial(year(

if x>2 then

firstmon:= dateserial(Year(currentdat

else

firstmon:= dateserial(Year(currentdat

datediff("w",firstmon, currentdate)+1