Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help finding week number based on counter field

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.

DateWeekWeek_counter
01/01/20171100
08/01/20172101
15/01/20173102
22/01/20174103
29/01/20175104
05/01/20176105
12/01/20177106

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

1 Solution

Accepted Solutions
sunny_talwar

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);

View solution in original post

3 Replies
sunny_talwar

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);

t_chetirbok
Creator III
Creator III

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

in your example for vDate = 9/10/2017

week is 201812

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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