Sorry. I have now added a TWBX to the original posting (above)
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.
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
CIc1.twbx 88.6 KB