How to calculate Last week Number

[Sarita_Kumari]

Hi All,

How should I calculate Last week number 

E.g If the Date is 2020-02-01 week number should be 4 and year should be 2021

  If the Date is 2020-01-04 week number should be 53 and year should be 2020

[afv2688]

Hello @Sarita_Kumari ,

Try using this on the formula tool:

IIF(ToNumber(DateTimeFormat([Field1],'%j'))<=7,
ToNumber(DateTimeFormat(DateTimeAdd([Field1],-7,'days'),'%W'))+1,
ToNumber(DateTimeFormat([Field1],'%W')))

Edit: This formula takes the current week except if the calculation refers to last year. If that happens it calculates the last years week by subtracting a whole week and adding then 1.

The counter starts at 0, therefore there is no need to subtract.

Regards

[Sarita_Kumari]

Hi,

Thanks for your reply. But I am looking for previous week number for current Date also.

[afv2688]

Hello @Sarita_Kumari ,

If you want the result to show it for the previous week using the current date you have only to change the field parameter:

IIF(ToNumber(DateTimeFormat(DateTimeNow(),'%j'))<=7,
ToNumber(DateTimeFormat(DateTimeAdd(DateTimeNow(),-7,'days'),'%W'))+1,
ToNumber(DateTimeFormat(DateTimeNow(),'%W')))

Given that Alteryx counts the weeks of the year starting by 0, on the counter, 2020-02-01 for alteryx belongs to the 4th week (4 weeks plus week 0).

So there is no need to subtract a week since it is already giving you the value you are looking for.

Regards