Week bucket

[krish2459]

Hi,

I need to caterorize 52 weeks into 26 weeks like  week1 and week2 as week 1 - week 2 2020 in the axis.

here is the calendar used.

MasterCalendar:

Load

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

'W' & Week(weekstart(TempDate)) & '-' & WeekYear(TempDate)as [WeekYear],

'Week ' & Div(TempDate-YearStart(TempDate)+WeekDay(YearStart(TempDate))+7,7) & '-' & WeekYear(TempDate) as WeekNumber,

Thanks..

[Vegar]

There is no week 53 in 2019 when using week function in QlikView.

[krish2459]

Hi Veger,

It's perfect.

But we need to add years to the buckets.

Thanks...

Thanks..

[Kushal_Chawda · Accepted solution]

try below

Calendar:
Load *,
     Week(Date) as Week,
     WeekYear(Date)&num(Week(Date),'00') as WeekYearNum,
     WeekYear(Date) as WeekYear;
Load date(MakeDate(2018)+IterNo()-1) as Date
AutoGenerate 1
While MakeDate(2018)+IterNo()-1<=Today();

week:
Load Distinct WeekYear as WeekYear1,
WeekYearNum,
Week as Week1
Resident Calendar;

WeekBucket1:
Load Distinct *,   
    if( Mod(RowNo(),2)=0,'Week '&Peek(Week1)&' '&peek(WeekYear1)&'-'&'Week '&Week1 &' '&WeekYear1,Null()) as Week_Bucket
Resident week
Order by WeekYearNum;

Drop Table week;

Drop Fields WeekYear1,Week1;

WeekBucket:
NoConcatenate
Load WeekYearNum, 
    if(isnull(Week_Bucket),Peek(Week_Bucket),Week_Bucket) as Week_Bucket
Resident WeekBucket1
Order by WeekYearNum desc;

Drop Table WeekBucket1;