Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
krish2459
Creator
Creator

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

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)

View solution in original post

Kushal_Chawda

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
Creator
Creator
Author

Any Input or required any clarifications.

 

Thanks..

Kushal_Chawda

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

krish2459
Creator
Creator
Author

Yes.

As you said.

 

Thanks..

Vegar
MVP
MVP

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

Kushal_Chawda

What if you have 53 weeks?

krish2459
Creator
Creator
Author

like week 53 - 2019  as week1-2020.

 

 

Thanks..

fred_s
Partner - Specialist
Partner - Specialist

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

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)

krish2459
Creator
Creator
Author

It's look good.

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

Thanks..