Week Analysis...............!!

[aveeeeeee7en]

Hi All

1.  I want Week categorization for every Month as Week 1, Week 2,...............

2.  Week should be from Monday to Sunday.

3.  The week should start only from 1st Monday coming in every Month.

Look at my Requirement below:

December 2014:
01-07  -  Week 1
08-14  -  Week 2
15-21  -  Week 3
22-28  -  Week 4
29-04  -  Week 5

January 2015:
05-11  -  Week 1
12-18  -  Week 2
19-25  -  Week 3
26-01  -  Week 4

February 2015:
02-08  -  Week 1
09-15  -  Week 2
16-22  -  Week 3
23-01  -  Week 4

March 2015:
02-08  -  Week 1
09-15  -  Week 2
16-22  -  Week 3
23-29  -  Week 4
30-05  -  Week 5

April 2015:
06-12  -  Week 1
13-19  -  Week 2
20-26  -  Week 3
27-03  -  Week 4

Regards

Av7eN

[rubenmarin]

Hi, seems working with this script:

Main:

LOAD *, 'Week ' & Ceil(Day(WeekStart(New_Date))/7) as Week;

LOAD

Date(MakeDate(2014,12,1)+RecNo()-1,'DD/MM/YYYY') AS New_Date

AutoGenerate(151);

[#3]

Hi Avee,

please look into this blog it is helpful to you....

Redefining the Week Numbers

[aveeeeeee7en]

Hi

Thanks for the reply.

The problem with your code is that it is bound to Particular Month only.

example: For December 2014 Month,  The Week 5 should consider all the below Dates:

29 December, 30 December, 31 December, 1 January, 2 January, 3 January & 4 January

29-04  -  Week 5

But as per your Logic the Week 5 2015 year is showing only 3 Dates i.e., 29 Dec, 30 Dec & 31 Dec

Regards

Av7eN

[rubenmarin]

Hi, I see all dates as per requirement, try it.

[rubenmarin]

Also, if you want month and year related to the start of the week, you should create those field based on weekstart:

Month: Month(WeekStart(New_Date)) as Month

Year: Year(WeekStart(New_Date)) as Year

[aveeeeeee7en]

Dear Sir

Your Logic is Working perfectly but I want the highlighted cases to be Fall under December Month.

My Final Requirement is to show Current Month vs. Last Month Week on Week Analysis..............!!

PFB:

PFA.

Regards

Av7eN

[rubenmarin · Accepted solution]

Hi, PFA.

Also, as we are transforming real dates, for current vs prev month can be easy creating a related field in number format to use in set analysis. Something like Year(WeekStart(Date))*12+Month(WeekStart(Date))-1

[aveeeeeee7en]

Thank You Sir

Conceptual but great.

Regards

Av7eN