× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Brian,

please go back an read my last post. You already got a complete solution for
your final query.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Brian
Gesendet: Monday, 01.12 2014 15:16
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Get Start Date of a Week Number

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!





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.