Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

How to find newly added customer yearly ?

Hi All ,

How to find newly added customer sales ?

My Requirement :

Capture2.PNG

Issue :

Capture3.PNG

Thanks & Regards

Shekar

1 Solution

Accepted Solutions
sunny_talwar

Here is QV12 solution

Sales by New Customer

Sum(Aggr(If(CustomerID = Above(CustomerID), 0, Sales), CustomerID, (Year_of_Sales, (NUMERIC))))

Sales by Returning Customer

Sum(Aggr(If(CustomerID = Above(CustomerID), Sales, 0), CustomerID, (Year_of_Sales, (NUMERIC))))

Capture.PNG

View solution in original post

14 Replies
Chanty4u
MVP
MVP

try

Firstsortedvalue(Dim,-Year)

shekhar_analyti
Specialist
Specialist
Author

please attach qvw

Chanty4u
MVP
MVP

sorry i dont have license version

sunny_talwar

May be this

Sum({$<CustomerID = e({1<Year_of_Sales={2014, 2015}>})>} Sales)

shekhar_analyti
Specialist
Specialist
Author

Hi Sunny ,

Here are my few doubts ..

Capture4.PNG

1) customer 279-01-1655 is newly added in 2016 , it should not be there in 2017 as its returning customer

2) In the set expression we are passing values 2014,2015 manually . what If my data is dump of 20 years and every year in need to see my new customers , is there any dynamic way ?

Thanks & Regards

Shekar

sunny_talwar

1) Exclude 2017 in your expression also

Sum({$<CustomerID = e({1<Year_of_Sales={2014, 2015, 2017}>})>} Sales)

2) There should be, but might need use of The sortable Aggr function is finally here!... do you have QV12 or above?

shekhar_analyti
Specialist
Specialist
Author

Hi Sunny ,

I do not have QV 12 but if you can give me Qlik 12 qvw , i can still see the solution .
Actually i have dump of 20 plus years and hence i needed an expression which should show me number of returning customers and their sales vs newly added customer sales ....

Thanks & Regards

Shekar

sunny_talwar

And you want to sum the sales of new customers each year which don't have any sales before or after that year where year is the dimension, right?

shekhar_analyti
Specialist
Specialist
Author

i want sales of all those customer which appeared for first time .
For example if i am doing purchase from alibaba for first time in year 2017 , then  i am new customer . however i can still do some purchase in year 2018 , 2019 .. but in that case i am a returning customer ...

so my requirement was something like year should be dimension . something like this ;

Capture6.PNG

Thanks & Regards

Shekar