Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Dmasvinc
Contributor III
Contributor III

Sales of New/lost customer

Hi

I need to calculate, SUM of a new and lost customer sales past two years.

Lost= made sales last year, but not the selected year

New= sales made selected year but not last year

 

1 Solution

Accepted Solutions
Taoufiq_Zarra

Hi,

Sorry, I forgot to answer.
attached my proposal

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

12 Replies
Taoufiq_Zarra

Hi,

Maye be this :

Lost

=sum({<Year = {"$(=Max(Year)-1)"}>} Sales)

New

=sum({<Year = {"$(=Max(Year))"}>} Sales)

olso for new just sum(Sales) can work

Cheers

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Dmasvinc
Contributor III
Contributor III
Author

doesnt work both expressions show same result just sum of my total sales..

I have 2014 and 2015 years  and i need just to make like comparison of customers that made sales in 2014,  but they gone in 2015. 

Taoufiq_Zarra

can you share some of your data with the requested outuput

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Dmasvinc
Contributor III
Contributor III
Author

 
 

Formula must show, old client sum = 2935+3520

New clients = 5373+2950+5290+2554+2037

Taoufiq_Zarra

if I consider the following data:

LOAD * INLINE [
    Customer, year, Sales
     2, 2018, 2
    3, 2019, 3
    4, 2017, 4
    5, 2019, 5
    6, 2019, 6
    1, 2018, 1
    2, 2019, 2
    3, 2015, 3
    4, 2014, 4
    5, 2019, 5
];

 

Capture.PNG

can you see this solution if it's okay ?


1- Straight or pivot table

2-as a dimension: Cusomer (for my case)


3- Expressions:


Sales for the previous year:

=sum({<year={$(=Max(year)-1)}>} Sales)

Sales for the current year:

=sum({<year={$(=Max(year))}>} Sales)

Old client :

=if(((sum({<year={$(=Max(year)-1)}>} Sales))>0) and ((sum({<year={$(=Max(year))}>} Sales))=0),sum({<year={$(=Max(year)-1)}>} Sales))


New Client:

=if(((sum({<year={$(=Max(year)-1)}>} Sales))=0) and ((sum({<year={$(=Max(year))}>} Sales))>0),sum({<year={$(=Max(year))}>} Sales))

 

I get as a result the table

Capture.PNG

 

Cheers,

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Dmasvinc
Contributor III
Contributor III
Author

if(((sum({<year={$(=Max(year)-1)}>} [item_ledger.sales_amount]))=0) and ((sum({<year={$(=Max(year))}>} [item_ledger.sales_amount]))>0),sum({<year={$(=Max(year))}>} [item_ledger.sales_amount]))

still doesnt work ...there just " - " result idk why.. 

Dmasvinc
Contributor III
Contributor III
Author

same goes and to old client 

Dmasvinc
Contributor III
Contributor III
Author

screenshot of table

Dmasvinc
Contributor III
Contributor III
Author

Oh, my calculations in Qlik sense, can you write formula for this program ?