Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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

1 Solution

Accepted Solutions
Highlighted

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

See attached example.


talk is cheap, supply exceeds demand
3 Replies
Highlighted

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

See attached example.


talk is cheap, supply exceeds demand
velthuisf
New Contributor III

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

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

Not applicable

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

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

Community Browser