I'm trying to figure out a formula to count the unique items in a database that has dates and items, by date. I've uploaded an example of what I'm to trying to do.
The database has items, the date of an initial price or price change, and the price.
Item Date Price
A 12/31/2010 $1.00
B 02/28/2011 $3.00
C 02/28/2011 $2.00
A 03/31/2011 $1.10
B 04/15/2011 $3.40
C 04/18/2011 $2.40
D 05/12/2011 $0.50
B 06/19/2011 $2.80
A 06/30/2011 NA
I'm trying to get a count of unique items on a given date. So in this example, there are no items at the start of December, 1 (item A) in January and February, 3 (A, B and C) in March. On April 1, there's still three; Item A was repriced, not added. On June 1, there are four items, but on July 1, there are only 3, because item A was dropped.
Date Items
12/01/2010 0
01/01/2011 1
02/01/2011 1
03/01/2011 3
04/01/2011 3
05/01/2011 3
06/01/2011 4
07/01/2011 3
08/01/2011 3
09/01/2011 3
10/01/2011 3
11/01/2011 3
12/01/2011 3
I've done it by hand for this example, but the real database is larger!
Thanks for any advice you have.
Bookmarks