How to search Year-Over-Year Rolling Weeks?

I have a user that would like to create a Pinboard that always starts with "last 10 weeks" of the current year and also those same comparable weeks in the prior year and prior-prior year for charting comparison purpose.  Then,  they'd like to be able to shift weeks historically as needed.   What's the best way to do this using either ThoughtSpot keywords or do I need to create new architecture to support a request like this?

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Greg Brown - One way it's possible is using rolling week (week of the year) column. If you have rolling week for each year (Current and past year), then using the below can help. For example

    Fact: Sales, Week of Year: rolling week, Filter: (rolling week < -10) VS: (2020 vs 2019 vs 2018)

    This will essentially give you sales across three years by week (same week of each year which is rolling week) and the filter would restrict for last 10 weeks for each year.


    The output would looks like

    Rolling week        Sales(2020)        Sales(2019)        Sales(2018)

    0                                S200                    S190                    S180

    -1                                S201                    S191                    S181

    ...                                ....                        ....                          ....

    -10                               S2010                    S1910                    S1810

    Let me know if this helps.

    Like 1
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 1Replies
  • 27Views
  • 3 Following