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.
mlmcc