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

[anjali0108]

Hi Ravindra,

Thank you so much for your help.

I am just thinking how will i link this script to the script I have shared as I have to use both.

Thanks a ton for replying.I,l try and will post again if this works.

[anjali0108]

Hi Ravindra,

Thanks a lot for this script and it is giving me the exact desired results that I want.

But in my existing application.

I am using a code like this and I want to merge your code with this table:

Common:

load *,

MonthName(CommonDate)  & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_MonthlyReports;

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);

And I want the new week number that you have formed to get linked with the above code.

So i wrote the above code first and then used ur code below that.But when I am reloading the system hangs.

Is it becuse of synthetic keys or some other issue is there.

I tried to use qualify * as well but that too dint help.

See the code I tried :

//creating month names and week names here

Common:

load *,

MonthName(CommonDate)  & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_MonthlyReports;

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);

//============================================================================================//

qualify *;

CommonTemp:

load

CommonDate,

CommonWeeks as WeekNum,

Date(WeekStart(CommonDate)+4) as WeekStartDate,

date(WeekEnd(CommonDate)+4) as WeekEndDate

Resident Common;

Intervals:

load CommonTemp.WeekStartDate, CommonTemp.WeekEndDate Resident CommonTemp;

temp:

IntervalMatch(CommonTemp.CommonDate) LOAD CommonTemp.WeekStartDate, CommonTemp.WeekEndDate

Resident Intervals;

left join

load CommonTemp.WeekStartDate, CommonTemp.WeekNum, MonthName(CommonTemp.WeekStartDate) as WSMonth Resident CommonTemp;

Agg_table:

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

temp2:

load CommonTemp.CommonDate, CommonTemp.WeekStartDate, CommonTemp.WeekNum, WSMonth Resident temp;

left join

load WSMonth, MSWN Resident Agg_table;

Final:

load CommonTemp.CommonDate ,

MonthName(CommonTemp.WeekStartDate) &'-'&(mod(CommonTemp.WeekNum,MSWN)+1) as Month_Week

Resident temp2;

drop Tables CommonTemp,Intervals,Agg_table, temp,temp2;

Please help.