Week Formula

[Nambiti]

Hi - Can anyone help me with a formula that will calculate sales for the last week. I know there is a formula called lastfullweek but what happens if the week finishes on a Wednesday? My report will run every Monday and needs to calculate sales for the week Monday - Sunday.
Thanks

 

[lbass]

Try the following for your record selection formula:

{table.date} in currentdate-dayofweek(currentdate)-5 to
currentdate-dayofweek(currentdate)+1

Or, if you are calculating within an existing report, then use a formula like the following for calculating:

if {table.date} in currentdate-dayofweek(currentdate)-5 to
currentdate-dayofweek(currentdate)+1 then {table.salesamt}

Then insert a summary on the formula to get the total for the week.

I think this should work on any day that you run the report, not just Monday.

-LB

 

[Nambiti]

Thanks Ibas - That works in the same way as lastfullweek.

What happens if the end of the month is a Thursday. Then I would only want to how sales for Monday to Thursday.

 

[lbass]

Try this for the record selection formula or add an if- then {table.salesamt} as shown above for a conditional formula:

{table.date} in currentdate-dayofweek(currentdate)-5 to
(
if month(dateadd("d",6, currentdate-dayofweek(currentdate)-5)) <> month(currentdate-dayofweek(currentdate)-5) then
date(year(currentdate), month(currentdate), 01)-1 else
currentdate-dayofweek(currentdate)+1
)

-LB