Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.
convert week number to week ending date
I have a PO forecasting report. I have a formula to give me the weeks as follows:
local NumberVar ThisWeek := datepart("WW",currentdate)
local NumberVar ThisYear := Year(currentDate);
if datepart("ww",{@Billing Date})=ThisWeek AND Year({@Billing Date}) = ThisYear then "Week 1"
else if datepart("ww",{@Billing Date}-7)=ThisWeek AND Year({@Billing Date}-7) = ThisYear then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek AND Year({@Billing Date}-14) = ThisYear then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek AND Year({@Billing Date}-21) = ThisYear then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek AND Year({@Billing Date}-28) = ThisYear then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek AND Year({@Billing Date}-35) = ThisYear then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek AND Year({@Billing Date}-42) = ThisYear then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek AND Year({@Billing Date}-49) = ThisYear then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek AND Year({@Billing Date}-56) = ThisYear then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek AND Year({@Billing Date}-63) = ThisYear then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek AND Year({@Billing Date}-70) = ThisYear then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek AND Year({@Billing Date}-77) = ThisYear then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek AND Year({@Billing Date}-84) = ThisYear then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek AND Year({@Billing Date}-91) = ThisYear then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek AND Year({@Billing Date}-98) = ThisYear then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek AND Year({@Billing Date}-105) = ThisYear then "Week 16"
else if datepart("ww",{@Billing Date}-112)=ThisWeek AND Year({@Billing Date}-112) = ThisYear then "Week 17"
else if datepart("ww",{@Billing Date}-119)=ThisWeek AND Year({@Billing Date}-119) = ThisYear then "Week 18"
else if datepart("ww",{@Billing Date}-126)=ThisWeek AND Year({@Billing Date}-126) = ThisYear then "Week 19"
else if datepart("ww",{@Billing Date}-133)=ThisWeek AND Year({@Billing Date}-133) = ThisYear then "Week 20"
else if datepart("ww",{@Billing Date}-140)=ThisWeek AND Year({@Billing Date}-140) = ThisYear then "Week 21"
else if datepart("ww",{@Billing Date}-147)=ThisWeek AND Year({@Billing Date}-147) = ThisYear then "Week 22"
else if datepart("ww",{@Billing Date}-154)=ThisWeek AND Year({@Billing Date}-154) = ThisYear then "Week 23"
else if datepart("ww",{@Billing Date}-161)=ThisWeek AND Year({@Billing Date}-161) = ThisYear then "Week 24"
else if datepart("ww",{@Billing Date}-168)=ThisWeek AND Year({@Billing Date}-168) = ThisYear then "Week 25"
else if datepart("ww",{@Billing Date}-175)=ThisWeek AND Year({@Billing Date}-175) = ThisYear then "Week 26"
And then I have a formula for each week that gives me the money sum amount for that week. I need to put the week ending date for the title above each total instead of the wording Week 1, Week 2, etc. I wrote the following formula which gives me the week ending date for week 1 but I can't get it to show in the report footer. This is the formula...
if {@Week Breakdown} = "Week 1" then {@week ending date}
The @week breakdown is the first big formula listed.
Anyone have any suggestions? Hope this makes sense.
local NumberVar ThisWeek := datepart("WW",currentdate)
local NumberVar ThisYear := Year(currentDate);
if datepart("ww",{@Billing Date})=ThisWeek AND Year({@Billing Date}) = ThisYear then "Week 1"
else if datepart("ww",{@Billing Date}-7)=ThisWeek AND Year({@Billing Date}-7) = ThisYear then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek AND Year({@Billing Date}-14) = ThisYear then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek AND Year({@Billing Date}-21) = ThisYear then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek AND Year({@Billing Date}-28) = ThisYear then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek AND Year({@Billing Date}-35) = ThisYear then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek AND Year({@Billing Date}-42) = ThisYear then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek AND Year({@Billing Date}-49) = ThisYear then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek AND Year({@Billing Date}-56) = ThisYear then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek AND Year({@Billing Date}-63) = ThisYear then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek AND Year({@Billing Date}-70) = ThisYear then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek AND Year({@Billing Date}-77) = ThisYear then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek AND Year({@Billing Date}-84) = ThisYear then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek AND Year({@Billing Date}-91) = ThisYear then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek AND Year({@Billing Date}-98) = ThisYear then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek AND Year({@Billing Date}-105) = ThisYear then "Week 16"
else if datepart("ww",{@Billing Date}-112)=ThisWeek AND Year({@Billing Date}-112) = ThisYear then "Week 17"
else if datepart("ww",{@Billing Date}-119)=ThisWeek AND Year({@Billing Date}-119) = ThisYear then "Week 18"
else if datepart("ww",{@Billing Date}-126)=ThisWeek AND Year({@Billing Date}-126) = ThisYear then "Week 19"
else if datepart("ww",{@Billing Date}-133)=ThisWeek AND Year({@Billing Date}-133) = ThisYear then "Week 20"
else if datepart("ww",{@Billing Date}-140)=ThisWeek AND Year({@Billing Date}-140) = ThisYear then "Week 21"
else if datepart("ww",{@Billing Date}-147)=ThisWeek AND Year({@Billing Date}-147) = ThisYear then "Week 22"
else if datepart("ww",{@Billing Date}-154)=ThisWeek AND Year({@Billing Date}-154) = ThisYear then "Week 23"
else if datepart("ww",{@Billing Date}-161)=ThisWeek AND Year({@Billing Date}-161) = ThisYear then "Week 24"
else if datepart("ww",{@Billing Date}-168)=ThisWeek AND Year({@Billing Date}-168) = ThisYear then "Week 25"
else if datepart("ww",{@Billing Date}-175)=ThisWeek AND Year({@Billing Date}-175) = ThisYear then "Week 26"
And then I have a formula for each week that gives me the money sum amount for that week. I need to put the week ending date for the title above each total instead of the wording Week 1, Week 2, etc. I wrote the following formula which gives me the week ending date for week 1 but I can't get it to show in the report footer. This is the formula...
if {@Week Breakdown} = "Week 1" then {@week ending date}
The @week breakdown is the first big formula listed.
Anyone have any suggestions? Hope this makes sense.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
I assume the big formula is Week Breakdown
What is the Week Ending Date formula?
Can you upload the report (even without data) so I can see what you are trying to do.
mlmcc
What is the Week Ending Date formula?
Can you upload the report (even without data) so I can see what you are trying to do.
mlmcc
Yes the big one is the week breakdown. The week ending is taking the billing date and giving the appropriate week ending date.
See attached.
PO_Forecast_26WK.rpt
See attached.
PO_Forecast_26WK.rpt
A formula in the report footer is only going to see the values in the last record in the report. {Week 1 Title} only displays something if PURCHASE_ORDER.DESIRED_REC
Assuming that I'm right and the problem is that the date in the last record isn't in "Week 1", then the question becomes, what are you trying to do with {Week 1 Title} in the report footer?
James
Assuming that I'm right and the problem is that the date in the last record isn't in "Week 1", then the question becomes, what are you trying to do with {Week 1 Title} in the report footer?
James
You need to create a formula for each week or one that loads an array with the dates then formulas to display the dates from the array.
Try this idea - put these where you want to display the dates.
Week1Date
Week2Date
Week3Date
Week26Date
mlmcc
Try this idea - put these where you want to display the dates.
Week1Date
WhilePrintingRecords;
Global DateVar dtWeek1;
dtWeek1 := CurrentDate - DayOfWeek(CurrentDate);
dtWeek1 + 7
Week2Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 *2
Week3Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 * 3
Week26Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 * 26
mlmcc
What I need is for the Titles at the end of the report to give me a week ending date instead of saying Week1, Week2, etc. I want the week ending date based on the billing date. I'll try your above formula and see if this works.
The labels won't change. Week numbers are based on current date.
According to the formula Week 1 if run today is 23 Dec 2017, the other week dates are determined from that.
Which box the values go in are determined by the billing date but the dates for the weeks are based on today.
mlmcc
According to the formula Week 1 if run today is 23 Dec 2017, the other week dates are determined from that.
Which box the values go in are determined by the billing date but the dates for the weeks are based on today.
mlmcc
He doesn't always use the current date (sorry should have said that). Some times he back dates like 8/1/2017 and that's a parameter box when the report is run.
Using the Billing Date doesn't work either. It gives me the current year calculated instead of using last year's date to calculate. It's got to be sort of like the Week Breakdown formula.
Try this as the week1 formula
Week1Date
mlmcc
Week1Date
WhilePrintingRecords;
Global DateVar dtWeek1;
dtWeek1 := CurrentDate - DayOfWeek({?Start Date});
dtWeek1 + 7
mlmcc
CurrentDate should be {?StartDate}
mlmcc
WhilePrintingRecords;
Global DateVar dtWeek1;
dtWeek1 := {?StartDate} - DayOfWeek({?Start Date});
dtWeek1 + 7
mlmcc
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
mlmcc