topic Week bucket in QlikView

Week bucket

krish2459 — Sat, 16 Nov 2024 18:15:58 GMT

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

Re: Week bucket

krish2459 — Wed, 05 Aug 2020 10:47:08 GMT

Any Input or required any clarifications.

Thanks..

Re: Week bucket

Kushal_Chawda — Wed, 05 Aug 2020 11:22:44 GMT

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

Re: Week bucket

krish2459 — Wed, 05 Aug 2020 12:00:08 GMT

Yes.

As you said.

Thanks..

Re: Week bucket

Vegar — Wed, 05 Aug 2020 13:13:13 GMT

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

Re: Week bucket

Kushal_Chawda — Wed, 05 Aug 2020 13:16:09 GMT

What if you have 53 weeks?

Re: Week bucket

krish2459 — Wed, 05 Aug 2020 13:29:06 GMT

like week 53 - 2019 as week1-2020.

Thanks..

Re: Week bucket

fred_s — Wed, 05 Aug 2020 13:45:17 GMT

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:

Re: Week bucket

Vegar — Wed, 05 Aug 2020 14:08:27 GMT

2019 does not have week 53, but 2020 does.

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)

Re: Week bucket

krish2459 — Wed, 05 Aug 2020 14:07:13 GMT

It's look good.

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

Thanks..

Re: Week bucket

Vegar — Wed, 05 Aug 2020 14:10:27 GMT

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

Re: Week bucket

krish2459 — Wed, 05 Aug 2020 14:37:48 GMT

Hi Veger,

It's perfect.

But we need to add years to the buckets.

Thanks...

Thanks..

Re: Week bucket

Kushal_Chawda — Wed, 05 Aug 2020 15:43:46 GMT

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;