Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Have you look this?
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
Try like this
MonthName(MakeWeekDate(Year(CommonDate), week(CommonDate))) &'_'& '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth;
Hi Avinash,
Thanks for the revert but it did not work.
And the week should start from thursday and end on friday.
Thanks.
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
try the code that i have shared on ur post .
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;