Avatar of lrollins
lrollins
Flag for United States of America asked on

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.
Crystal Reports

Avatar of undefined
Last Comment
lrollins

8/22/2022 - Mon
Mike McCracken

Are you using a cross tab for this?

mlmcc
lrollins

ASKER
No I'm not using cross tabs
Mike McCracken

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
lrollins

ASKER
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
James0628

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_RECV_DATE is in the right week, so if the date in the last record in the report is not in that week, {Week 1 Title} won't display anything.  A simple check would be to put DESIRED_RECV_DATE in the report footer and see if it's in "Week 1".

 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
Mike McCracken

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
WhilePrintingRecords;
Global DateVar dtWeek1;
dtWeek1 := CurrentDate - DayOfWeek(CurrentDate);
dtWeek1 + 7

Open in new window


Week2Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 *2

Open in new window


Week3Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 * 3

Open in new window

...
Week26Date
evaluateAfter({@Week1Date});
Global DateVar dtWeek1;
dtWeek1 + 7 * 26

Open in new window


mlmcc
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lrollins

ASKER
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.
lrollins

ASKER
Current date won't work.  I have to base it off of the billing date.
Mike McCracken

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
lrollins

ASKER
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.
Mike McCracken

Ok.  So use the parameter value instead of current date.

Is the parameter optional?

mlmcc
lrollins

ASKER
I'm on vacation til January 2nd. I'll try this when I get back.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lrollins

ASKER
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.
Mike McCracken

Try this as the week1 formula

Week1Date
WhilePrintingRecords;
Global DateVar dtWeek1;
dtWeek1 := CurrentDate - DayOfWeek({?Start Date});
dtWeek1 + 7

Open in new window


mlmcc
lrollins

ASKER
No that didn't work.  My ?Start Date is 8-1-17 and it returns dtWeek1 as 1/12/18.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
lrollins

ASKER
That worked.  Thanks
lrollins

ASKER
Thank you so much.