topic Re: Help finding week number based on counter field in QlikView

Help finding week number based on counter field

hopkinsc — Wed, 20 Dec 2017 14:12:16 GMT

Hi all,

i have a date variable and a calendar which includes a week_counter field. what i am trying to do is find the date variable in the calendar and retrieve the associated week number - store this to a variable. then i want to go back 52 weeks from the week number found in the previous step but by using the week_counter field.

eg.

Date	Week	Week_counter
01/01/2017	1	100
08/01/2017	2	101
15/01/2017	3	102
22/01/2017	4	103
29/01/2017	5	104
05/01/2017	6	105
12/01/2017	7	106

so in the above, if my variable date was 12/02/2017 and i want to go back 4 weeks, then i want the following...

1. store week no '7' to a variable (which is the corresponding week for the variable date of 12/01/2017)

2. find the corresponding Week_counter for week 7 (which is 106), then go back 4 (which is 102) and store the corresponding week (which is '3') to another variable.

i want to use the counter field because this takes any leap yrs and other customer variables into account in my customers data.

i am happy with the variable option as stated above, but if its easier then a flag in the data would be beneficial instead, so a new field called YTD which is flagged as 1 if the week numbers are in the current range (so in the above example, there would be a 1 against weeks 3-7)

can anyone help with this please?

i have attached a sample.

thanks in advance

Re: Help finding week number based on counter field

sunny_talwar — Wed, 20 Dec 2017 14:23:08 GMT

Something like this

LET vDate = Date('09/10/2017');

Temp:

LOAD Counter as DateCounter

FROM Calendar.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Date = '$(vDate)';

LET vCounter = Peek('DateCounter') - 52;

DROP Table Temp;

Calendar:

LOAD Date,

[FiscYr/Week],

Counter,

If(Counter = $(vCounter), 1, 0) as OneYearAgoFlag

FROM Calendar.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Help finding week number based on counter field

t_chetirbok — Wed, 20 Dec 2017 14:23:09 GMT

only({<Counter={"$(=only({<Date={'$(vDate)'}>}Counter)-4)"}>} [FiscYr/Week])

in your example for vDate = 9/10/2017

week is 201812

Re: Help finding week number based on counter field

hopkinsc — Wed, 20 Dec 2017 14:57:11 GMT

thanks again both. i used Sunny's again so i was able to create the new flag field instead of using variables in the charts.

thanks