I am trying to build a report in NetSuite where I am showing the weekly sales by item for the past 12 week. The SQL formula i currently have works well but i recently realized that when the year changed, my formula was not able to pull the data in.

case when ((to_char(to_date({transaction.trandate}),'WW')) = (to_char({today},'WW')- 6)) then {transaction.quantity} else 0 end

in the second part of the formula when i substract 6 weeks from todays weeks, it doesnt return any data because the week number goes to a negative value rather than the second week of december from last year. How can i amend this query so that i can get the last 12 weeks rolling data without being affected by a change in the year.

Thanks in advance.

CodePudding user response：

If you calculate the current weeknumber with that, then it's 4.
And 4-6=-2 But 6 weeks back in 2021 it was 50.

But there are 7 days in a week. So you can calculate the week number of today-(6*7).
And include the year while at it.

case 
when ((to_char(to_date({transaction.trandate}),'YYYYWW')) = (to_char({today}-6*7,'YYYYWW'))) 
then {transaction.quantity} 
else 0 
end