1 2 Previous Next 16 Replies Latest reply on Jan 31, 2015 4:58 AM by Jonathan Drummey

    Find Earliest/First/Least and/or Latest/Last/Maximum of a Field and Use that for Other Fields

    Jonathan Drummey

      There are various cases for analyses of cohorts where we want the earliest, first, least, latest, last, and/or maximum of some field available to other rows in the data for calculations.

       

      In this case, using Superstore Sales the goal is to end up with a # of days since the first Order Date for each subsequent Order Date for each Customer Name. So we need to make the first Order Date for each Customer Name available to other calculations.

       

      There are 4 solutions demonstrated in this workbook:

       

      - Blend Diff. A self-blended data source is used to provide the first order date as an aggregate.

      - Subquery #1 - agg Diff. A subquery using a GROUP BY clause is used to get the first order date as a dimension.

      - Subquery #2 - JOIN Diff. A subquery using a self-join is used to get the first order date as a dimension, in an indexed data source this can be faster than a GROUP BY.

      - TC Diff. A table calculation is used to generate a measure for the first order date, then a datediff calc gets the data.

       

      Can you come up with any additional solutions? I'm particularly interested in solutions that don't require Custom SQL.

        1 2 Previous Next