Current Week Query

[gbrozak]

I'm looking to do something very simple: I would like to identify the current week in a report I'm generating of college student visits. In other words, I would like to show a text line that reads:

 

Recruiting Visits for the week of: March 5-9, 2008

 

Any help?

 

THANKS!

[touchMe]

Your weeks run Wednesday through Sunday? huh?

 

Any date falls within a given week, that week can be expressed as a number (sometimes fiscal etc.,) or as a date range running usually Mon-Fri, Sun-Sat or whatever... in your case it runs Wed-Sun (it don't matter if this is wrong, same rules apply)

 

Given any date, FMP will provide a calculated day name for that date, build a date/text calculation based on date of visit (any date during the visit would work unless the visit spans ? 2 weeks

 

If the date of my visit is 08/06/08 FMP knows this is a Wednesday, and if my weeks run (as example) mon-fri and the dayName does not = monday, it ain't too hard a calculation to say... this visit is for week Monday 4th - 9th June 2008

 

What you gotta watch for (the fun bit!) is where the week runs through two months or two years, play with the date function calcs and you should get it, and if the report is required ONLY for current week... hey! (as Fonzie used to say)

[gbrozak]

touchMe,

 

My example was indeed random. Our week runs M-F.

 

I appreciate your comments. Can you provide any examples of calculations for me to try? I'm afraid I'm not having any luck on my own.

[touchMe]

I would build a calc like this... a bunch of, if this and that stuff, or you could use case statements. You could also compress everything into one calculation (and I will leave that for you to do), but then it's easier to trace, test and audit when things are in small packets, (note your email address if you want it sent as a file) so...

 

You need a date field and you have one in your visit date, it doesn't matter if the date is the start of visit date, or end of visit date... just so long as it is a date which falls within the period of the visit itself, you will need this field to start the calculation... can we just call this field [date] (change in calc to your own field name)

 

Find the records you want for the report, the calculation will take the [date] from the first record of the found set. Create and define four new (calculation) fields as follows:

 

1. dayCalc=DayName(date)

 

2. weekstartingDate=If(dayCalc="monday", date, If(dayCalc = "tuesday", date - 1, If(dayCalc="wednesday", date-2, If(dayCalc = "thursday", date - 3, If(dayCalc = "friday", date - 4, If(dayCalc = "saturday", date - 5, date - 6))))))

 

3. weekendingDate=weekstartingDate+5

 

4. Result="Recruiting visits for week: " & If(Month(weekstartingDate) = Month(weekendingDate), Day(weekstartingDate) & " - " &Day(weekendingDate) & " " & MonthName(weekstartingDate) & " " & Year(weekstartingDate), Day(weekstartingDate) & " " & MonthName(weekstartingDate) & " - " & Day(weekendingDate) & " " & MonthName(weekendingDate) & " " & Year(weekendingDate))

[touchMe]

define fields 1. and 4. as text, 2. and 3. as date