Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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..