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

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

• ###### 1. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

The best solution I'd have for you is to set up a start date and end date parameter from the Order Date field.

That's what I've done and then replaced 'COUNTD(Order Date)' from your formula with 'DateDiff('day', [StartDate], [EndDate])... so that regardless of the entries for that customer in that date range, it'll count those days.

1 of 1 people found this helpful
• ###### 2. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

You need to know the number of days in a month. The formula for that is

In other words take date, truncate it to the first day of the month, add a month to get to the first day of the next month, take away a day to get the last day of this month and tell me  what day number that is.

1 of 1 people found this helpful
• ###### 3. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

I had considered this solution as well but my concern is that it will get all days in the month even if there were no orders for that day for any customers. For example, if I use this approach in December, Christmas Day will be included yet it's a holiday which could drive down the metric for months with multiple holidays.

• ###### 4. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

This presumes you need the number of days - the only thing that makes me think otherwise is that there are 31 days in Jan.

• ###### 5. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

Ah, just saw your response. What if there are just no orders for a normal day (3rd Jan) - would that ever happen?

• ###### 6. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

I'm not sure that it ever would as our volume is extremely high but I could see where that could be an issue if that scenario were to arise.

• ###### 7. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

So you could use total(countd([Date])) set appropriately.

This does not solve the situation above with a missing day.

There are two approaches to that:

• Make a list of vacation dates and blend this. Use as part of the calculations
• Much easier as we only care about the month of the holiday, something like:

case datepart('month',[Date]) when 1 then 1

when 4 then 2

when 6 then 1

else 0 end

Or whatever the number of holidays there are in each month

And use the number of days calculation above minus this calculation

1 of 1 people found this helpful
• ###### 8. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

After further review, we are going to consider all days regardles of whether or not there were orders.

I am looking into both solutions provided.  Many thanks again!

• ###### 9. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

Thanks to both Alex and Darin.  BOTH answers were CORRECT! But the forum will only allow me to select one as the "Correct Answer". I opted to use Alex solution for now, but may switch to the parameters.

• ###### 10. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

Is there way to make the formula you provided dynamic so that if I run the query for more than one month or for a year that it will count the number of days?

• ###### 11. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

Completely unrelated, but thanks for this answer!  I was trying to work out how to get the previous monthly value in tableau and your answer made everything clear for me!

• ###### 12. Re: calculate count of orders for a customer based on all dates during the period, not just dates where said customer placed an order

So I was never able to get those formulas from Alex to work for anything more than a single month and went with your solution using parameters...wish I could change this thread to show your answer as the CORRECT answer.

However, I did have to change your formula very slightly. Since there are actually 31 days in January, the answer should be .03226 (1 order id / 31 days). in order to get this you must add 1 day back to the date diff.

COUNTD([Order ID])/DateDiff('day', [StartDate],[EndDate]+1)

Thanks again to all!