topic Re: Sales of New/lost customer in App Development

Sales of New/lost customer

Dmasvinc — Sun, 08 Dec 2019 15:47:32 GMT

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

Re: Sales of New/lost customer

Taoufiq_Zarra — Sun, 08 Dec 2019 16:28:22 GMT

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

Re: Sales of New/lost customer

Dmasvinc — Sun, 08 Dec 2019 16:37:47 GMT

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.

Re: Sales of New/lost customer

Taoufiq_Zarra — Sun, 08 Dec 2019 16:49:10 GMT

can you share some of your data with the requested outuput

Re: Sales of New/lost customer

Dmasvinc — Mon, 09 Dec 2019 18:32:51 GMT

Formula must show, old client sum = 2935+3520

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

Re: Sales of New/lost customer

Taoufiq_Zarra — Mon, 09 Dec 2019 19:29:21 GMT

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 ];

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

Cheers,

Re: Sales of New/lost customer

Dmasvinc — Mon, 09 Dec 2019 20:11:41 GMT

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..

Re: Sales of New/lost customer

Dmasvinc — Mon, 09 Dec 2019 20:12:12 GMT

same goes and to old client

Re: Sales of New/lost customer

Dmasvinc — Mon, 09 Dec 2019 20:21:56 GMT

screenshot of table

Re: Sales of New/lost customer

Dmasvinc — Mon, 09 Dec 2019 20:47:43 GMT

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

Re: Sales of New/lost customer

Taoufiq_Zarra — Tue, 10 Dec 2019 07:36:07 GMT

can you share your qliksense file ?

Re: Sales of New/lost customer

Dmasvinc — Tue, 10 Dec 2019 08:22:45 GMT

Yes. I use field item ledger sales amount as measure, and customers name as dimension, also date year.

Re: Sales of New/lost customer

Taoufiq_Zarra — Tue, 17 Dec 2019 19:02:54 GMT

Hi,

Sorry, I forgot to answer.
attached my proposal