?
Solved

Forecasting payables for this year and next

Posted on 2011-10-24
4
Medium Priority
?
237 Views
Last Modified: 2012-05-12
I have the following formula and it works good but it needs an extra tweek:

local NumberVar ThisWeek := datepart("WW",currentdate);
if datepart("ww",{@Billing Date})=ThisWeek  then "Week 1"
else if datepart("ww",{@Billing Date}-7)=ThisWeek  then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek  then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek  then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek  then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek  then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek  then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek  then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek  then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek  then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek  then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek  then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek  then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek  then "Week 16"

Anything over sixteen weeks shows as future costs.  The problem I'm having is that it's picking nov 2012 as week one and it shouldn't.  Week1 should be showing for Nov 2011.  Can anyone help?
0
Comment
Question by:lrollins
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37017961
The problem (assuming there is one) is in @Billing Date, not this formula.
0
 
LVL 1

Author Comment

by:lrollins
ID: 37017994
This is my BillingDate formula:

cdate(DateAdd ("D",30 , {PURCHASE_ORDER.DESIRED_RECV_DATE}))

It was for 60 days but they just had me change it and it works fine now, but what if I have to change it back to 60 days?

Thanks
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 37018008
A week in the year is the same.  You need to check the year as well.

The ww option of DatePart returns the week number of the date based on that year.  So given today is 24 Oct 2011, the date 52 weeks from now 22 Oct 2012 will return the same week number.

 
local NumberVar ThisWeek := datepart("WW",currentdate);
local NumberVar ThisYear := Year(currentDate);
if datepart("ww",{@Billing Date})=ThisWeek AND Year({@BillingDate}) = ThisYear then "Week 1" 
else if datepart("ww",{@Billing Date}-7)=ThisWeek  AND Year({@BillingDate}-7) = ThisYear  then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek  AND Year({@BillingDate}-14) = ThisYear  then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek  AND Year({@BillingDate}-21) = ThisYear  then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek  AND Year({@BillingDate}-28) = ThisYear  then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek  AND Year({@BillingDate}-35) = ThisYear  then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek  AND Year({@BillingDate}-42) = ThisYear  then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek  AND Year({@BillingDate}-49) = ThisYear then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek  AND Year({@BillingDate}-56) = ThisYear  then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek  AND Year({@BillingDate}-63) = ThisYear  then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek  AND Year({@BillingDate}-70) = ThisYear  then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek  AND Year({@BillingDate}-77) = ThisYear  then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek  AND Year({@BillingDate}-84) = ThisYear  then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek  AND Year({@BillingDate}-91) = ThisYear  then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek  AND Year({@BillingDate}-98) = ThisYear then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek  AND Year({@BillingDate}-105) = ThisYear  then "Week 16"

Open in new window


I think there might be an easier formula
Something like

 
Local NumberVar DayDiff;
Local NumberVar WeekDiff;
DayDiff := DateDiff('d',CurrentDate,{@BillingDate});
WeekDiff := Day Diff \ 7;
"Week " CStr(WeekDiff + 1,0,"")

Open in new window


mlmcc
0
 
LVL 1

Author Closing Comment

by:lrollins
ID: 37018189
Thanks so much!!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
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 …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 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