3 Replies Latest reply on Nov 20, 2018 3:34 PM by Zhouyi Zhang

    Using aggregates to "flatten" values for a set of records to derive a dimension--any workaround?

    Ryan Blosser

      Hello,

       

      What I have are IDs with multiple rows that have a series of start and end date (think slowly changing dimensions) and what I need to do is take the min of the start date for the rows associated with same set of IDs and the max of the end dates for the same ID to derive a "lifetime".  The viz would have "lifetime" on the columns (0 to whatever), and the Y axis is the number of Ids per lifetime value (actually a window function that says "at least x IDs lasted this long"...)


      Example for the data:

       

       

      ID Start End

      1     1/1     1/8

      1     1/8     1/14

       

      this ID would have an age of 13 (1/14 minus 1/1).  So I figured, no big deal, use LOD to get the min, LOD to get the max, subtract the two, keep the row with the min date in the viz and count only those.  Problem is, I can't convert that datediff calculation to a dimension (my assumption is because I am using an aggregate before this calculation), so bringing it into the viz just makes a sum of the rows, which won't help me.

       

      Is there any workaround for this in Tableau?  The attached has one wrinkle that certain rows cannot count in the calculation, I can probably add a datasource filter so they aren't even in there, but I don't think it changes this problem much.