topic Re: Week creation in QlikView

Week creation

anjali0108 — Tue, 28 Feb 2017 05:09:08 GMT

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

Re: Week creation

Anil_Babu_Samineni — Tue, 28 Feb 2017 05:38:13 GMT

Have you look this?

Redefining the Week Numbers

Re: Week creation

avinashelite — Tue, 28 Feb 2017 05:43:35 GMT

check this too

Week Start - Sunday instead of Monday

Redefining the Week Start

Re: Week creation

anjali0108 — Tue, 28 Feb 2017 10:51:40 GMT

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

Re: Week creation

avinashelite — Tue, 28 Feb 2017 11:57:27 GMT

Try like this

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

Re: Week creation

anjali0108 — Tue, 28 Feb 2017 12:13:34 GMT

Hi Avinash,

Thanks for the revert but it did not work.

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

Thanks.

Re: Week creation

Chanty4u — Tue, 28 Feb 2017 12:16:10 GMT

Hi Anjali,

Even am facing the similar kind of issue

Re: RE:MonthEnd

Re: Week creation

avinashelite — Tue, 28 Feb 2017 12:16:53 GMT

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

Re: Week creation

anjali0108 — Tue, 28 Feb 2017 12:43:14 GMT

try the code that i have shared on ur post .

Re: Week creation

Tue, 28 Feb 2017 15:09:52 GMT

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;

Re: Week creation

anjali0108 — Wed, 01 Mar 2017 05:55:29 GMT

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.

Re: Week creation

anjali0108 — Wed, 15 Mar 2017 04:55:00 GMT

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.