4 Replies Latest reply on Jun 25, 2014 6:00 AM by Michael Boldin

    Problem with Table Display of Dates and Lagged Values

    Michael Boldin

      I found solutions to both sets of problem reported below by poking around the forums.  See my follow up messages for the 'tricks' I learned.

      I am confounded with 2 problems in the attached workbook where I try to display a table of lagged values of a single series.

      In Sheet 1 I have almost what I want, except I would rather display the Date as a single column, not Year+Month.  When I reset the date item to be 'Exact Date', Sheet 2 looks very different. In fact it is no longer  a table but a chart and Table is disabled in Show Me. 

      How can I easily display the date as single Month-Year column (Ie January-2012), and why is Sheet 2 so different with just the simple change of how a column shelf is set ?  I can use a Yearm dimension I created to show 1 column, but this dimension is not treated as a date and creates other problems


      Second problem is with 6 month lags where I use WINDOW_AVG([value],-6,-6) in a  new calculated field.  Here I do not want to display all 12 months in a year.  The problem is I seem to need to trick the display by hiding rows—see the WithHiddenRows sheet.   But if I then deselect some rows, the 6 month lag is really a 6 row lag.  See the Dashboard below where the MonthsDeSelected table has lagged6months values that are not the 6 month ago values. Defining the table calculation to be by date, not rows does not fix the problem. 



      What I desire is to guarantee that a WINDOW function is based on the exact calendar lag time that I define and would never be affect by rows displayed.  Of course I can use an SQL join to guarantee this with the raw data, but I would like to be able to do is dynamically change the lag. I created a lag measure to test this idea but the lag is always based on the selected dates, not always exactly N months  back.

        • 1. Re: Problem with Table Display of Dates and Lagged Values
          Patrick A Van Der Hyde

          Hello Michael Boldin,


          Do you have the workbook mentioned to share? 


          -- Patrick

          • 2. Re: Problem with Table Display of Dates and Lagged Values
            Michael Boldin

            Sorry. I have now added a TWBX to the original posting (above)

            • 3. Re: Problem with Table Display of Dates and Lagged Values
              Michael Boldin

              I figured out how to solve one part of the problems I faced, but the problem of setting a fixed calendar interval as a  lag length remains.


              First, it is easy to create almost any date display  format as a new calculated Dimension (right click on Date --> Create Custom Date).


              Also, my sheets had Aggregate Measures (in the Analysis Menu item) unchecked.  Re-checking Aggregate Measures makes a world of difference -- even though I am working with single values in each cell for a given date that is not really an aggregate when it is used and displayed as an sum() or avg() of a single item.  The problem seems to be in some cases the table view would not work because more than one value in non-aggregated form was trying to be placed in a single table cell.


              I still do not know if it is possible to 'fix' a lag length to be a set calendar interval  -- as opposed to depending on the filter of dates.  I realize I can replicate what I want by hiding columns or rows, or pre-creating lags in the imported data, but I would prefer something more flexible and straightforward in a Tableau workbook. I would greatly appreciate any insight or suggestions for this issue.

              • 4. Re: Problem with Table Display of Dates and Lagged Values
                Michael Boldin

                I believe I have figured out a robust work around for creating and displaying value lags.


                See attached workbook where Table1 does not show a full set of lags and percentage changes for the year 2011. Here the problem is the date filter is applied before the lag calculation 

                Table2 solves the problem by creating a new Measure yearx= lookup(min(year([Date])),0) I then use yearx as a quick filter and I do not lose the early 2011 lagged values.


                You can play with the Lag slider to change this parameter and see how Table1 and Table2 behave differently.


                Note the yearx=min(Date) (or another aggregator function) is needed to use the date calculation as a Measure, otherwise it seems to be put in the Dimension group.  It was not necessary to add the year() function-- I just wanted to screen by year not year+month.  But Lookup() is needed to use the calculated field as filter.  Finally, I placed yearx as pill on the Marks shelf to activate the quick filter option.  You should be able to drag directly to the Filter shelf and use as a regular filter.


                It helps to edit the table calculation of yearx to be sure it operates over 'date' not rows or columns (or an automatic selection).  Also at one stage I saw the option 'Ignore in Table Calculations'  for yearx (from a right click on a pill in the Measure shelf) put this option seems to appear and disappear based on other selections.


                In essence, it seems  Dimension filters take precedent over Measure calculations and you need to prevent this 'feature'.  Just create  a Measure filter that is operationally equivalent to the desired Date Dimension filter and the lag calculation will not be 'filtered' out.


                FYI the trick is also described in this forum Ideas thread


                and the clue about the effects of filter precedents came from