LastFullWeek-1 ????

[noxid001]

Hi all, hoping you can help.

Im reporting based on weekly counts and need to show the last 6 weeks. Im use an If / then formula and just sum the results.
For the week in progress I use:

if (({Main.Complaint Date})) in WeekToDateFromSun then
if {Product_Type.Product type}like "*@ctive*" then 1 else 0

for the previous week I use:

if ({Main.Complaint Date}) in (astfullweek then
if {Product_Type.Product type}like "*@ctive*" then 1 else 0


but how do I show week -2, -3 etc as I can't work out the syntax.

Also is it possible to show the date (dd/mm/yyyy) of the first sunday in each week - i.s so I can have a list of dates running in a column that are the first sunday of the week.

Many thanks

Malcolm.

 

[lbass]

Is there a reason you are not inserting a group on date set to print on change of week? Or do you want a crosstab layout with weeks going across the top? Again, the simplest approach would be to use {table.date}->group options->print on change of week. This would automatically show the Sunday date as that is the default week start.

In either case you could limit your records in the record selection formula by using:

{table.date} in dateadd("ww",-5, currentdate-dayofweek(currentdate)+1) to currentdate

-LB

 

[noxid001]

thanks Ibass your a star,

The "WW" bit is what i was missing to resolve the first part of my query (weeks before LastFullWeek) although I'm still stuck with the list of dates, im doing a manual cross tab and although i want the dates as the row to do a group effects the data.

Basically need a formula to give the the date (dd/mm/yyyy) of the Sunday just been, then another formula for the sunday before that etc.

Hope someone can help

thanks

Malcolm.

 

[lbass]

Please show a sample of what you want your report to look like. You should be able to use a formula like:

dateadd("ww",-5, currentdate-dayofweek(currentdate)+1)

...to give you the Sunday dates. Just change the -5 for each week to -4, -3, etc. If you want to display weekly data, why would inserting a weekly group "affect the data" in an unwanted way? You could still use a detail level formula that checks the product type.

-LB

 

[noxid001]

that formula works a treat, thanks a lot for all your help today

Malcolm.