How to calculate new customers and new products compared to last year

[#1]

Hi,

I am a bit stuck trying to calculate new customers this year compared with last year. Hope somebody can help me.

I am working on detailed transaction data.

Data:

Year

Date

SalesAmt

ProductId

CustomerId

I am looking to answer the following questions:

1. What customers have I sold to this year (2013) that I did not sell to last year.

2. How many customers have I sold to this year that I did not sell to last year

3. What is the amount I have sold this year to new customers

Regards,

Freddy

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[Anonymous]

Looks great, have to figure out if it is the coreect answer for my model.

Can you please explain the syntax a little bit:

sum ({<CustomerId=p({<Year={'$(=only(Year))'}>}CustomerId), CustomerId=e({<Year={'$(=only(Year)-1)'}>}CustomerId)>} SalesAmt)

?? (all customer ID's of selected year , all customers ID's of last year that where not available in selected year)

Message was edited by: velthuisf

[#4]

Thanks a lot!

This was very helpful.

However, I have a problem with the count of new customers.

Would it be possible to make a count of customers with sales>0 in 2013 and with sales <=0 in 2012?

Freddy