14 Replies Latest reply on Dec 10, 2012 3:51 PM by Alex Kerin

    calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

    Kevin Taylor

      I'm attempting to create a calculated field that will bring back the count  of orders per day for a particular customer for a given month. Here is my current formula:

       

      COUNTD(Order ID) / COUNTD(Order Date)

       

      Problem with the formula above is it divides the Count of Order IDs by the number of dates that the customer had transactions instead of dividing by the total number of dates in the filtered month.

       

      In the example I have attached, using the Superstore Sales data, Customer Adam Hart had one order in January, 2012. Since there were 30 unique order dates in January, I would like my formula to divide by 30 instead of 1.

       

      So the result is Orders per Day = 1 but I am trying to get the result to = .03333

       

      Many thanks for any light that can be shed on this!

       

      Kevin