Re: Get Start Date of a Week Number

[Brian <belstsrv@xxxxxxxxx>]

Hello!  Sorry for the delayed reply, I was away for the holiday and 
missed these replies.

In the data I am getting now, I get a date.  Instead of having a column 
in the output for every date I get, I instead use a simple function to 
take the date I have and return back the week of the year that this date 
belongs to.  To do this, I just use SQL like this:

Set :$myWeek = Week_ISO(:$myDate)

I could use just "Week" instead of "Week_ISO", if that makes other 
conversions or work easier.  I just chose "Week_ISO" without any firm 
reason for doing so.

In my output, I do end up with 52 date columns.  For a particular item, 
if they provided quantity information for an item for 2 dates that fell 
in the same week, I'd just accumulate that quantity and output the sum 
total in the column for that corresponding date.

I also have logic to determine the earliest week that I've received data 
for so I can start my output with that week number and create 52 columns 
from there.  For example, if it is later in the year and the first 
quantity we received starts in week 45, then my columns will start in 
that week and have 51 more after that.  I thought that was a cleaner way 
to position the output for the user to show them what is most relevant.

So, I was looking for a simple way to just convert "Week 1" to 
"1/1/2015" (or whatever the appropriate Monday would be).  But, maybe 
there is a different or better way to do it?

Thank you again.

On 11/28/2014 11:50 PM, Booth Martin wrote:
> I love these kinds of problems.  :)  Questions for you:  You speak of 
> already  grouping the sales by week.
> What method are you using for that, because  that would probably point 
> to a similar way to get the date for the week.
> Are you are needing   52 columns for each report?
> Is column 1 always the first week of the year?
>
> On 11/26/2014 2:31 PM, Brian wrote:
> > Thank you for the feedback.  In the forecast I am getting arbitrary 
> > dates
> > and quantities.  The person getting the report simply wanted the 
> > quantities
> > accumulated by week.  So, if I got data for Monday and Tuesday of the 
> > same
> > week, I would add those together.
> >
> > In my report, I had a row for each item and then a column for each week.
> > It might look like this:
> >
> > Item, Week 1, Week 2
> > ABC,344,200
> >
> > And so on.
> >
> > After generating that output, the user then asked to have the start date
> > for the week show instead of the week number so now it night look like:
> >
> > Item, 2014-12-29, 2015-01-05
> > ABC,344,200
> >
> > The 12/29 and 01/15 dates were just from a calendar I was looking 
> > at.  I do
> > not think it matters at all if the definition of the week is the ISO 
> > one or
> > not.  The main thing is that I am consistent and put dates as the column
> > headings and not week numbers anymore.
> >
> > I think Birgitta's solution will work, but I am not in a place to try it
> > out at the moment.
> >
> > If you've got other thoughts though, just let me know.
> >
> > Thanks to all who replied so far!