Week creation

[anjali0108]

I am using below script to create WeekNoAndMonth:

Common:
load *,
MonthName(CommonDate) & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth;

load *,
if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

LOAD *,
MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

FROM
$(AppQvdPath)Common.qvd
(qvd);

WeekNoAndMonth is the dimension that I am using at last in the chart.

Requirement:

I want to add one more field here in script level only that can show me the last 8 weeks as values like

MonthName  & '- Week' & ' ' & WeekNumberInMonth but the week start is friday and week end is Thursday.

but here my weeks formation should be like:

Dimension:

Jan 2017 - Week 1: 6th to 12th feb

Jan 2017 - Week 2: 13th to 19th feb

Jan 2017 - Week 3: 20th to 26th feb

Jan 2017 - Week 4: 27th to 2nd feb

Feb 2017 - Week 1: 3rd to 9th feb

Feb 2017 - Week 2: 10th ro 16th feb

Feb 2017 - Week 3:  17th to 23rd feb

Thanks in advance

[Anil_Babu_Samineni]

Have you look this?

Redefining the Week Numbers

[avinashelite]

check this too

Week Start - Sunday instead of Monday

Redefining the Week Start

[anjali0108]

Hi Avinash,

I have already redefined the week start in script.

The requirement is I want to create the dimension in the same script that I am using on top to create the last 8 weeks.

I need the below weeks:

Jan 2017 - Week 1: 6th to 12th feb

Jan 2017 - Week 2: 13th to 19th feb

Jan 2017 - Week 3: 20th to 26th feb

Jan 2017 - Week 4: 27th to 2nd feb

Feb 2017 - Week 1: 3rd to 9th feb

Feb 2017 - Week 2: 10th ro 16th feb

Feb 2017 - Week 3:  17th to 23rd feb

the dimension i have used in my script is creating weeks like:

Jan 2017 - Week 1: 6th to 12th feb

Jan 2017 - Week 2: 13th to 19th feb

Jan 2017 - Week 3: 20th to 26th feb

Jan 2017 - Week 4: 27th to 31st jan

Feb 2017 - Week 1: 1st to 2nd feb

Feb 2017 - Week 2: 3rd to 9th feb

Feb 2017 - Week 3: 10th ro 16th feb

Feb 2017 - Week 4:  17th to 23rd feb

Hope you understood the requirement.

I need two dimensions to show monthly and weekly  reports as there week formations are different.

please help asap.

Thnks

[avinashelite]

Try like this

MonthName(MakeWeekDate(Year(CommonDate), week(CommonDate))) &'_'& '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth;

[anjali0108]

Hi Avinash,

Thanks for the revert but it did not work.

And the week should start from thursday and end on friday.

Thanks.

[Chanty4u]

Hi Anjali,

Even am facing the similar kind of issue

Re: RE:MonthEnd

[avinashelite]

could you share the sample data ...I need to check how your defining the dates and start of the week ,,,,so that I could help you out

[anjali0108]

try the code that i have shared on ur post .

[#10]

raw_data:

LOAD date(@1) as CommonDate,

Week(@1) as WeekNum,

Date(WeekStart(@1)+4) as WeekStartDate,

date(WeekEnd(@1)+4) as WeekEndDate

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Intervals:

load WeekStartDate, WeekEndDate Resident raw_data;

temp:

IntervalMatch(CommonDate) LOAD WeekStartDate, WeekEndDate Resident Intervals;

left join

load WeekStartDate, WeekNum, MonthName(WeekStartDate) as WSMonth Resident raw_data;

Agg_table:

load WSMonth, Min(WeekNum) as MSWN resident temp group by (WSMonth) ;

temp2:

load CommonDate, WeekStartDate, WeekNum, WSMonth Resident temp;

left join

load WSMonth, MSWN Resident Agg_table;

Final:

load * , MonthName(WeekStartDate) &'-'&(mod(WeekNum,MSWN)+1) as Month_Week

Resident temp2;

drop Table raw_data,Intervals,Agg_table, temp,temp2;