Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
only({<Counter={"$(=only({<Date={'$(vDate)'}>}Counter)-4)"}>} [FiscYr/Week])
in your example for vDate = 9/10/2017
week is 201812
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