Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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