3 Replies Latest reply on Nov 15, 2018 10:14 AM by Joe Oppelt

    LOD over previous 12 rows

    Sagar Kavitake

      Hi All,

       

      I was working on a requirement wherein we wanted to show AVG of Sales for the last 12 weeks.

      Initially, I tried it using WINDOW function and it worked pretty well until I was asked to add a filter on [Week] field. When I filtered on the week the window reduced, so did the AVG.

      (See attached sample)

       

      I tried using thee Fixed LOD but I am not able to figure out the Range part in it.

       

      -Sagar

        • 1. Re: LOD over previous 12 rows
          Jim Dehner

          Hi

          you attached a twb workbook which does not contain data - please attach a twbx book with the data included

           

          when you filter data you are excluding data from the underlying table that supports the viz - the usual approach is to use a parameter on date and base the window_sum calculation on the parameter

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: LOD over previous 12 rows
            Joe Oppelt

            You can't do positional calcs with LOD (unless you can identify position by the data in the rows.)

             

            Stick with the table calc, but instead of doing a quick filter, make a table calc filter.  The quick filter removes rows from the underlying table.  The table calc filter leaves the underlying table intact, so that table calcs will still work.

             

            Is [Week] its own dimension field?  Or are you using Tableau's date functionality to identify week?

             

            To make a table calc filter the standard syntax for a common one is this:

             

            LOOKUP( ATTR([dimension name]) , 0 )

             

            The zero says to get whatever is entered in the filter for the current row (and acts on all rows that way.)

             

            So if WEEK is its own dimension, just shove week in there.  If you're using Tableau's date functionality, drag your current week filter pill into the calc editor in place of [dimension name] above, and Tableau will expand the proper syntax for that right into the calc editor.

             

            If you have trouble with this, upload a sample workbook (even a superstore example) that demonstrates what you have, and I'll show you.

            1 of 1 people found this helpful
            • 3. Re: LOD over previous 12 rows
              Joe Oppelt

              PS:  Once you create that calc, put that on the filter shelf in pace of your current filter.  Do "show filter", and if you did it right, it will look just like your original quick filter.