Peek Function and First 3 values of YYYYWW (Year + Week)

[jayanttibhe]

Hi,

I am trying got get the values from last 3 weeks (Current +  last 2 weeks) in the Format YYYYWW – i.e. for 2^nd week of year it’s 201502.

Following code is not working when I am having values as 201501 as current week. It gives last week as 201400 and previous to that as 201499 which is incorrect.

I simply want top 3 weeks values as : 201501, 201452, 201451.  Or As per the order in the DB .

Please find attached simple Qvw file and suggest.

Please let me know what else can be done to achieve it in Qlikview.

[Oleg_Troyansky]

Hay Jayant,

You can't operate with weeks as simple 6-digit numbers. Instead, you should work with dates and use date/time functions to calculate the particular year, week, etc... Then, you can use Date math to calculate current and previous weeks. Consider using functions like:

WeekStart(), WeekEnd(), etc...

best,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

you Need to provide an individual calculation for each week

The following code gives you the second and third week of the selected maxwk!

But be Aware that sone years have 53 weeks. Só use dá date function like Oleg suggested

for second week

=if ((right(only(maxwk),2))<=1,(left(only(maxwk),4)-1)&51-right(only(maxwk),2)
,(left(only(maxwk),4))&right(only(maxwk),2)-1)

or third week

=if ((right(only(maxwk),2))<=2,(left(only(maxwk),4)-1)&52-right(only(maxwk),2)
,(left(only(maxwk),4))&right(only(maxwk),2)-2)

[Clever_Anjos]

This should help you:

LET vDateLq = peek('maxwk',0,'twoweeks');

LET vDateeLq = MakeWeekDate(LEFT($(vDateLq),4),Right($(vDateLq),2)) -7 ;

LET vDateeLq = year($(vDateeLq)) & NUM(Week($(vDateeLq)),'00');

LET vDateeeLq = MakeWeekDate(LEFT($(vDateLq),4),Right($(vDateLq),2)) -14 ;

LET vDateeeLq = year($(vDateeeLq)) & NUM(Week($(vDateeeLq)),'00');

[maxgro · Accepted solution]

maybe (one maxwk selected)

=WeekYear(MakeWeekDate(left(maxwk,4), Right(maxwk,2))-7)

&  num(week(MakeWeekDate(left(maxwk,4), Right(maxwk,2))-7), '00')

and then replace -7 with -14, -21

[jayanttibhe]

Thanks Oleg / Ridolf / Clever / maxgro for quickly replying. I have used the solution given by Clever / Maxgro.

Thank you very much guys.

[MarcoWedel]

please don't delete attachments for other community members to be able to follow this thread.

thanks

regards

Marco