Sales of New/lost customer

[Dmasvinc]

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

[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

[Dmasvinc]

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

[Dmasvinc]

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

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,

[Dmasvinc]

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]

same goes and to old client 

[Dmasvinc]

screenshot of table

[Dmasvinc]

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