2 Replies Latest reply on Apr 28, 2012 5:35 PM by Michael Greenwell

    Summarizing a subset of already filtered data based on dynamic criteria

    Michael Greenwell

      I am trying to summarize the most recent period from the periods that are part of the Date Range in the actual filter. I can generate the title by using MAX, but I can't use MAX to pull out the data itself in a calculation as that would require a mixture of aggregate and non-aggregate data, which makes Tableau weep. Can anyone suggest how I might fill in the summary information in the table on the top right with aggregate data from the most recent period in the table? The values in question are calculated using SummaryPeriodSpread, SummaryPeriodGoal, SummaryContractors, SummaryStarts, and SummaryStops, and I am currently just pulling them from a hard coded week. How might I dynamically determine the most recent period so that I can use these calculations to pull out this summary information?

        • 1. Re: Summarizing a subset of already filtered data based on dynamic criteria
          Richard Leeke

          You should be able to do this with a table calculation.

           

          I've put asimple example together using the Tableau Coffee Sales example data. There are 4 sheets:

           

          Sheet 1 just has monthly data listed. Note the field [Is Latest Period?]. That uses a table calculation to compare the date on each row with the date for the last row.

           

          On sheet 2 I have put a copy of [Is Latest Period?] on the filter shelf and filtered to just the last row (i,e, where that field = True).

           

          On sheet3 I have moved the [Date] and [Is Last Period?] fields from Rows to Level of Detail - and it's broken it. This is because the default behaviour when I introduced the [Is Last Period?] field in sheet 1 was that "Compute Using" was set to "Table Down" - i.e. it was finding the last row in the table by going down the page. But now that I've moved Date to Level of Detail - Tableau doesn't knwo what "down the page" means.

           

          On sheet4 I've corrected this by explicitly setting Compute Using to [Date]. That means it will order the rows in date order, rather than relying on the order shown in the view. Note that you need to have the Compute Using setting consistent for the [Is Last Period?] field on Level of Detail and on the Filter shelf (the partitioning definition is set independently each time a table calculation is used). The best thing is to set it in Level of Detail and then copy that to the Filter shelf (hold down the ctrl key, click on the copy of Level of Detail and drag it to the filter shelf).

          • 2. Re: Summarizing a subset of already filtered data based on dynamic criteria
            Michael Greenwell

            That worked perfectly, thanks so much for your help.