Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
krish2459
Contributor III
Contributor III

Week bucket

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..

Labels (1)
2 Solutions

Accepted Solutions
Vegar
Partner
Partner

2019 does not have week 53, but 2020 does.

Vegar_0-1596636498994.png

 

 

As your dimension is circular I would probably kept the last week as a single week bucket like in the picture above.

'Week '&(Week(Date)-1+ Mod(Week(Date),2))&'-' & rangemin(Week(Date)+ Mod(Week(Date),2), 53)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Kush
MVP
MVP

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;

 

Annotation 2020-08-05 164321.png

View solution in original post

12 Replies
krish2459
Contributor III
Contributor III
Author

Any Input or required any clarifications.

 

Thanks..

Kush
MVP
MVP

Do you need buckets for two subsequent week? Like Week3- Week4, Week 5- Week6...?

krish2459
Contributor III
Contributor III
Author

Yes.

As you said.

 

Thanks..

Vegar
Partner
Partner

Take a look at this script and especially my bold part creating the two week bucket.

LET BucketSize = 2;

For _date = YearStart(today()) to YearStart(today(),1)

LOAD
dayname($(_date)) as Date,
Week($(_date)) as Week,
'Week '& (Week($(_date))-1+ Mod(Week($(_date)),$(BucketSize)))& '-week ' & (Week($(_date)) + Mod(Week($(_date)),$(BucketSize))) as Weekbucket
AutoGenerate 1;
next

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Kush
MVP
MVP

What if you have 53 weeks?

krish2459
Contributor III
Contributor III
Author

like week 53 - 2019  as week1-2020.

 

 

Thanks..

fred_s
Partner
Partner

Hi,

Just another way to look at your problem 🙂

test:
LOAD *,
if(EVEN(Weekno) = 0, 'WeekBucket-' & num(Weekno + 1,'00'), 'WeekBucket-' & num(Weekno,'00')) as Bucket
;


LOAD * INLINE [
Weekno, Justaltr
1, a
2, b
3, c
4, d
5, e
6, f
];

 

This will give you Buckets like:

weekbucket.png

Vegar
Partner
Partner

2019 does not have week 53, but 2020 does.

Vegar_0-1596636498994.png

 

 

As your dimension is circular I would probably kept the last week as a single week bucket like in the picture above.

'Week '&(Week(Date)-1+ Mod(Week(Date),2))&'-' & rangemin(Week(Date)+ Mod(Week(Date),2), 53)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

krish2459
Contributor III
Contributor III
Author

It's look good.

But I need week 53 - 2019 as week 1 -2020.

Thanks..