3 Replies Latest reply on Mar 23, 2017 2:22 PM by Ginger Macfarlan

    Rolling Year over Year Relative Date Filter

    Ginger Macfarlan

      My data has a list of week ending dates and the corresponding sales for that week. To analyze this data, I look at rolling week aggregates (Last Week vs LastYear Last Week, L4 vs. LYL4, etc), which is why I have to use a relative date filter.

       

      I found a really clever tutorial here that helped me set up a rolling date filter using a set and a parameter, relative to the max date in the data (not TODAY or a fixed date). I also love that setting up the parameter as a list allows for simple clicking to the date aggregate I want to see in the view.

       

      This would be a perfect solution, except that it's limited to this year. I still have no way to compare rolling aggregates year over year. My first thought was to create an adjusted date field, -1 year, then create a new set/parameter combo from that, but that didn't get me what I needed. Ideally, I would love to have This Year and Last Year aggregates displayed in the same table, with headers distinguishing, but this may be impossible.

       

      One thought: my data also has a field for the retailer-specific week, which is a string field that specifies the week of the fiscal year (i.e., 201701, 201702, all the way up to 201752, when it starts over at 201801). If I converted this string field to a number, and could somehow tell Tableau to reference the retailer week corresponding to the MAX week ending date, minus 100, that could work. So, if the MAX date is retailer week 201635, find the date corresponding to 201535, and calculate the aggregate from that. But I'm not sure if that's feasible.

       

      Hopefully the packaged workbook makes this a little clearer.

        • 1. Re: Rolling Year over Year Relative Date Filter
          Vasil Petkov

          Hi Ginger,

           

          You can do what you are looking for with a FIXED calculation, create this calculation:

           

          {max(int([Retailer Week]))} - int([Retailer Week]) < [Date Aggregate]

          or

          (({max(int([Retailer Week]))}-100) - int([Retailer Week]) < [Date Aggregate]

          and ({max(int([Retailer Week]))}-100) - int([Retailer Week])>=0)

           

          and use it as a filter.

           

          Best,

          -V

          1 of 1 people found this helpful
          • 2. Re: Rolling Year over Year Relative Date Filter
            Ginger Macfarlan

            This was just what I needed, thank you!

             

            From here, I was able to get a nice tabular format like I wanted by creating new Retailer Fiscal Year and Retailer Week of Year fields. Reattaching an updated twbx for reference of anyone who might find this useful.

            • 3. Re: Rolling Year over Year Relative Date Filter
              Ginger Macfarlan

              Hi Vasil, I've run into a limitation for this solution. The aggregate filter doesn't work when the fiscal year starts over--for example, trying to pull the last 13 weeks from Retailer Week 201806 will only return the 8 weeks since the beginning of the new fiscal year. I'm reattaching the twbx with updated dates to illustrate the problem.

              Ginger

               

              Edit: I solved my problem by creating a Record ID field in my data. I was able to add this step to my TDE; I understand there may be a way to do this through SQL. The Record ID is tied to the retailer week so the oldest week in the data is always "1" and the newest week is always "104." I used the aggregate filter calc to refer to the Record ID field rather than the retailer week. Again, not an ideal solution, since it required more data prep outside Tableau, but good info to have, and a solution to a really tricky issue.