topic Re: How to calculate new customers and new products compared to last year in QlikView

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

Wed, 13 Feb 2013 19:12:14 GMT

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 — Wed, 13 Feb 2013 19:34:26 GMT

See attached example.

Anonymous — Wed, 13 Feb 2013 21:14:56 GMT

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

Wed, 13 Feb 2013 21:21:49 GMT

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