how to get week

[kunkumnaveen]

HI All,

i am trying to put days under particular weeks,which is not working .

if(Day='1,2,3,4,5,6,7','Week1',if(Day='8,9,10,11,12,13,14','Week2',if(Day='15,16,17,18,19,20,21','Week3',if(Day='22,23,24,25,26,27,28,29,30,31','Week4')))) as Week

can any one suggestion where it went wrong

qt1:

Load*,

Dual(Date(Date,'MMMM') ,Month(Date(Date,'MMM'))) as Month,

day(Date) as Day,

//'Week' & Week(Date) as Week;

LOAD date#(Date( Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Date,

          quantity

FROM

(ooxml, embedded labels, table is Sheet1);

load Day,

if(Day='1,2,3,4,5,6,7','Week1',if(Day='8,9,10,11,12,13,14','Week2',if(Day='15,16,17,18,19,20,21','Week3',if(Day='22,23,24,25,26,27,28,29,30,31','Week4')))) as Week

Resident qt1;

[lironbaram]

hi

your if statement is wrong

here is another version that should work

qt1:

Load*,

Dual(Date(Date,'MMMM') ,Month(Date(Date,'MMM'))) as Month,

day(Date) as Day,

//'Week' & Week(Date) as Week;

LOAD date#(Date( Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Date,

          quantity

FROM

(ooxml, embedded labels, table is Sheet1);

load Day,

ceil(Day/7) as Week ///this will create your week number in much more efficient way

Resident qt1;

[kunkumnaveen]

HI

if i use ceil function it coming out 5 weeks in a month,but i need only 4 weeks for all the months

[kunkumnaveen]

Even this is not working ,i am getting   ')' missing  error but everything looks ok

if(Day <=7,'Week1',  if(Day >7<=14 ,'Week2', if(Day >14<=21,'Week3', if(Day >21<=31,'Week4')))) as Weeks

[lironbaram · Accepted solution]

hi

in this case , change the function to :

load Day,

if(ceil(Day/7)>4,4,ceil(Day/7)) as Week ///this will create your week number in much more efficient way

Resident qt1;

[kunkumnaveen]

perfect man,thanks very munch...it worked

[kunkumnaveen]

can u plz explain me wat exactly this is doing ..plz

if(ceil(Day/7)>4,4,ceil(Day/7)) as Week

[vinieme12]

You should read this post and it has links to other week manipulation posts embedded

Recipe for a 4-4-5 Calendar

[MarcoWedel]

Hi,

also possible:

RangeMin(Ceil(Day/7),4) as Week

hope this helps

regards

Marco