Solved

Forecasting payables for this year and next

Posted on 2011-10-24
Medium Priority
237 Views
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
Question by:lrollins
• 2

LVL 77

Expert Comment

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

LVL 1

Author Comment

ID: 37017994
This is my BillingDate formula:

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

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"
``````

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,"")
``````

mlmcc
0

LVL 1

Author Closing Comment

ID: 37018189
Thanks so much!!!
0

Featured Post

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
Course of the Month17 days, 4 hours left to enroll