troubleshooting Question

convert week number to week ending date

Avatar of lrollins
lrollinsFlag for United States of America asked on
Crystal Reports
18 Comments1 Solution212 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros