Counting Unique records by Month in Tableau over a year

Question

I have a list of product codes and their sale dates. I want to only count the first ever sale of that product, then SUM the first sales by month of sale. So if Product A was sold in Feb, Mar and Nov, it only gets counted in Feb.

Here is a badly copied table

Product Code    Sales Date      WHAT I WANT
-------------------------------------------
A1234          01/02/2014       COUNT THIS
B3333          03/02/2014       COUNT THIS
C5555          06/03/2014       COUNT THIS
A1234          09/03/2014       DON’T COUNT
F567           15/04/2014       COUNT THIS
P000           25/05/2014       COUNT THIS
B3333          01/01/2015       DON’T COUNT
A1234          06/01/2015       DON’T COUNT
P000           19/01/2015       DON’T COUNT
B3333          26/01/2015       DON’T COUNT
K5678          20/02/2015       COUNT THIS

I want my end result to be a graph with months down the side and then a count of first times a product is sold.

Does this make sense? I tried using MIN, but it makes everything AGG, which stops you comparing a date with a date.

Tableau 9 introduces a new type of calculation called level of detail (LOD) calculations that will make this type of calculation much easier. Release should be very soon. You can read about LOD calcs in several posts on their blog. Here is an introductory post tableau.com/about/blog/2015/2/… — Alex Blakemore

tagyoureit tagyoureit · Accepted Answer · 2019-04-30T04:01:41

UPDATE: commenter pointed out there were two questions in the initial question. Both would be answered with an LOD.

To answer the question: "I want my end result to be a graph with months down the side and then a count of first times a product is sold."

Use a Fixed formula:

count(if 
  DATETRUNC('month',[Sales Date])={fixed [Product Code]:min(datetrunc('month',[Sales Date]))}
then
  [Product Code]
END)

This formula compares the month of the date at each row level to the minimum date for all sales of that same product code. Then it counts up the records where this condition is true.

To answer the question: I want to only count the first ever sale of that product, then SUM the first sales by month of sale.

Since the OP didn't provide sales #'s in the original table I didn't answer the sum part of the question. However, this would also be an LOD and it would look like:

sum(if 
  DATETRUNC('month',[Sales Date])={fixed [Product Code]:min(datetrunc('month',[Sales Date]))}
then 
  [Sales]
END)

This is very similar to the above except it sums up sales instead of counting products.

And here is a picture (I added sales and one extra row of sales for product A1234 so you can see that it gets summed as part of the product's first months sales.)

To deep dive into the LOD (Level of Detail) part of this, {fixed [Product Code]:min(datetrunc('month',[Sales Date]))}, Tableau allows you to do calculations based on a specified scope. In this case, the formula gets read like "return the minimum date's month of the sales date for each product code". You can read much more about LOD's on Tableau's site or many Google search results.

Counting Unique records by Month in Tableau over a year

1 Answers