I have a set of data from which I can show the distinct set of customers who ordered something in 2011 and the distinct set who ordered something in 2012. Some customers ordered in both years, some in only one of the years. I can see the total difference in the customer base between the two years, but what I want to be able to show is the number of customers who are 'lost' (ordered in 2011 but not in 2012) and the number who are 'new' (ordered in 2012 but not 2011).
I'm sure this is possible, but my table calculation skills are not yet up to that level. Basic sample of data attached.
Many thanks in advance for any help.
Hi Donna,
By creating a calculated field similar to the following should get you the desired output:
if window_sum(countd([Customer (copy)]))=2 then "Returned"
elseif countd([Customer (copy)])=1 and max([Ordered Year])=2012 or (isnull(countd([Customer (copy)])) and max([Ordered Year])=2011) then "New"
elseif countd([Customer (copy)])=1 and max([Ordered Year])=2011 or (isnull(countd([Customer (copy)])) and max([Ordered Year])=2012) then "Lost"
end
Place this on the filter shelf and Exclude Null.
Hope this helps!
-Tracy
