4 Replies Latest reply on Oct 18, 2017 7:59 AM by Preethi Kumar

    Creating a calculated field for order sequence

    Preethi Kumar

      Hi - There are several questions related to using index and rank in these forums.

       

      Most of the questions are regarding using index / rank field within the same sheet as a table calculation. But what I'm trying to do is create a calculated field called "order sequence" that I can use as a filter in several sheets that will be part of my dashboard.  I'm not sure if I can use either rank or index in this scenario.

       

      I define order sequence as follows. For every user id, the order sequence should be assigned based on order date.

       

      User ID       Order Date         Order Sequence

      A                 1/1/2016               1

      A                 1/10/2017             2 

      B                 5/1/2017               1

      C                 4/1/2016               1

      C                 4/10/2017             2

      C                 8/1/2017               3

       

      I would like to calculate metrics like year to date / month to date revenue, Average Order Value, order count etc. for all first orders (order sequence = 1) and then the same metrics for all repeat orders (order sequence !=1)

       

      I've attached a sample workbook.

       

      Any suggestions on how to do this is greatly appreciated!

       

      Thank you.