How do you set a table to automatically go back 2 fiscal weeks for a report?

Barb · 2024-04-24T19:20:58+00:00

There was an error rendering this rich post.

I have a client with a custom fiscal calendar. I have a report that automatically returns data from the previous fiscal week (Sunday to Saturday). I need a second report that will return the same data from two fiscal weeks ago. Does anyone know how I can automatically have the fiscal week updated for this report?

Find more posts tagged with

Accepted answers

ArborRose

Something like this?

Last week

case 
  when YEAR(`Date_Column`) = YEAR(CURRENT_DATE() - INTERVAL 1 WEEK)
  and WEEK(`Date_Column`, 1) = WEEK(CURRENT_DATE() - INTERVAL 1 WEEK, 1)
  then `amount`
end

week previous

case 
  when YEAR(`Date_Column`) = YEAR(CURRENT_DATE() - INTERVAL 2 WEEK)
  and WEEK(`Date_Column`, 1) = WEEK(CURRENT_DATE() - INTERVAL 2 WEEK, 1)
  then `amount`
end

All comments

ArborRose

Something like this?

Last week

case 
  when YEAR(`Date_Column`) = YEAR(CURRENT_DATE() - INTERVAL 1 WEEK)
  and WEEK(`Date_Column`, 1) = WEEK(CURRENT_DATE() - INTERVAL 1 WEEK, 1)
  then `amount`
end

week previous

case 
  when YEAR(`Date_Column`) = YEAR(CURRENT_DATE() - INTERVAL 2 WEEK)
  and WEEK(`Date_Column`, 1) = WEEK(CURRENT_DATE() - INTERVAL 2 WEEK, 1)
  then `amount`
end

Barb

I'll give that a shot and let you know what happens!! Thank you!!