Sales of new/loyal/lost customers

[jorgen_santberg]

Hi all,

I am trying to get the sum of sales of customers in a chart with:

- sales this year & no sales last year (=NEW customers)

- sales last year & no sales this year (=LOST customers)

- sales this year & sales last year (=LOYAL customers)

My time dimensions are Month, MonthYear and Year

All help is welcome!

[olivierrobin]

hello

try with set analysis like :

if(sum({<Year={'$(=Max(Year)'}>}sales) +sum({<Year={'$(=Max(Year)-1'}>}sales) ,'LOYAL'),

if(sum({<Year={'$(=Max(Year)'}>}sales)>0 and sum({<Year={'$(=Max(Year)-1'}>}sales)=0,'LOST',

  if(sum({<Year={'$(=Max(Year)'}>}sales)=0 and sum({<Year={'$(=Max(Year)-1'}>}sales)>0,'NEW'

  )

)

)

[sunny_talwar]

May be look here

Set Analysis - New and Lost Customers with Nested Advanced Search - Harvesting Wisdom

[sasiparupudi1]

May be something like below(Ex Loyal)

Count(

IF(Sum({Year={"$(=Max(Year))"}}Sales)>0 AND Sum({Year={"$(=Max(Year)-1)"}}Sales)>0, Customers)

)

[effinty2112]

Hi Jorgen,

There are bound to be syntax errors here but these suggestions might help

NEW
Sum({$<Year = {$(=max(Year))}, Customer = E({<Year={$(=-1+max(Year))}>} Customer)>}Sales)

LOST
Sum({$<Year = {$(=-1+max(Year))}, Customer = E({<Year={$(=max(Year))}>} Customer)>}Sales)

LOYAL
Sum({$<Year = {">= $(=-1+max(Year))"}, Customer -= E({<Year={">=$(=-1+max(Year))"}>} Customer)>}Sales)

Cheers

Andrew