topic Re: how to get week in QlikView

how to get week

kunkumnaveen — Thu, 06 Oct 2016 06:41:53 GMT

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;

Re: how to get week

lironbaram — Thu, 06 Oct 2016 06:58:09 GMT

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;

Re: how to get week

kunkumnaveen — Thu, 06 Oct 2016 08:58:05 GMT

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

Re: how to get week

kunkumnaveen — Thu, 06 Oct 2016 09:06:45 GMT

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

Re: how to get week

lironbaram — Thu, 06 Oct 2016 09:26:03 GMT

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;

Re: how to get week

kunkumnaveen — Thu, 06 Oct 2016 09:29:23 GMT

perfect man,thanks very munch...it worked

Re: how to get week

kunkumnaveen — Thu, 06 Oct 2016 09:31:23 GMT

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

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

Re: how to get week

vinieme12 — Thu, 06 Oct 2016 09:35:13 GMT

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

Recipe for a 4-4-5 Calendar

Re: how to get week

MarcoWedel — Thu, 06 Oct 2016 21:50:32 GMT

Hi,

also possible:

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

hope this helps

regards

Marco