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

Options
Barb
Barb Member
in Charting & Analyzer

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?

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Barb
    Barb Member

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

Categories