YearWeek -1

[stabben23]

Hi,

I have a YearWeek field 201401 (YYYYWW) if I want to step 1 week back i'll get 201400.

I have test with two different:

Dual(WeekYear(Date)&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) as [Year Week],

num(WeekYear(Date)&num(Week(Date),00)) as [Year Week Num],

These are numeric and thats why I get 201400, but how to get the YerWeek field as a date so it will show 201352?

I will use the field in a set analysis.

[tresesco]

WeekName(MakeWeekDate(Left('201401',4), Right('201401',2)),-1)                          //'-1'  refers one week back

[stabben23]

Hi,

a little bit closer, if we use a date like 20140101 how can we format this to a yearweek that is non numeric?

[CELAMBARASAN]

Try something like this

=Replace(WeekName(Date#(20140101, 'YYYYMMDD')), '/', '')

[#5]

One solution could be to add an "index" for the YearWeek in your time table with AutoNumber:

AutoNumber(YearWeek, 'YearWeekID') as [YearWeekID];

Then you can just do simple integer calculating on this "index", e.g. the YearWeeID.

You just need to get the ID of your actual YearWeek in a variable.

Hope it helps

[stabben23]

That will give me 201400 if I add -1

[jagan · Accepted solution]

Hi,

Try this

=Replace(WeekName(Date#(20140101, 'YYYYMMDD'), -1), '/', '')

Regards,

Jagan.

[stabben23]

,

Thank you guys.