Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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)
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;
Any Input or required any clarifications.
Thanks..
Do you need buckets for two subsequent week? Like Week3- Week4, Week 5- Week6...?
Yes.
As you said.
Thanks..
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
What if you have 53 weeks?
like week 53 - 2019 as week1-2020.
Thanks..
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:
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)
It's look good.
But I need week 53 - 2019 as week 1 -2020.
Thanks..