I came up w/ this formula in Excel since that's where the data will be analyzed:
=INT((E2-WEEKDAY(E2)-DATE(YEAR(E2)-(MONTH(E2)<4),4,1))/7)+2
Where 4/1 is the start of our fiscal year. You'd need to adjust things as appropriate if your fiscal year starts in June or October or whatever.
I created a number of BAQs and published them via Epicor Portal and consume them in Excel. I can refresh them and get live data, and include Excel formulas and even update the BAQs or published Portal Views and the new columns, etc. make their way to Excel.
Pretty slick.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of homestyleindustries
Sent: Monday, March 01, 2010 1:07 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Calculating Weeks in Vantage
I setup a report for some production data via Crystal Reports for my company and was able to lump data by weeks. I just created a group within the report and sorted/grouped by a date field. An option at the bottom of the group setup allowed for grouping by each day, each week, each two weeks, each month, etc.
=INT((E2-WEEKDAY(E2)-DATE(YEAR(E2)-(MONTH(E2)<4),4,1))/7)+2
Where 4/1 is the start of our fiscal year. You'd need to adjust things as appropriate if your fiscal year starts in June or October or whatever.
I created a number of BAQs and published them via Epicor Portal and consume them in Excel. I can refresh them and get live data, and include Excel formulas and even update the BAQs or published Portal Views and the new columns, etc. make their way to Excel.
Pretty slick.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of homestyleindustries
Sent: Monday, March 01, 2010 1:07 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Calculating Weeks in Vantage
I setup a report for some production data via Crystal Reports for my company and was able to lump data by weeks. I just created a group within the report and sorted/grouped by a date field. An option at the bottom of the group setup allowed for grouping by each day, each week, each two weeks, each month, etc.
--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> I need to provide one of my business units some visibility into their
> sales backlog, bucketed by week.
>
>
>
> I'd also like to give them visibility at the same time into fiscal
> period boundaries.
>
>
>
> Our fiscal year starts in April.
>
>
>
> Progress doesn't provide a WEEK() function. Excel does, and I could
> go the BAQ->Epicor Portal route to give them the data in Excel. Excel
> has a Week() function as well, and its probably just simplest to bring
> the data I want into a refreshable worksheet and fill an extra column
> with the week number.
>
>
>
> Just wondering what other folks have done here.
>
>
>
> --
>
> Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
> Picometrix
>
> bspolarich@...
> <mailto:bspolarich@...> ~ 734-864-5618 ~
> www.advancedphotonix.com <http://www.advancedphotonix.com>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links