Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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'
)
)
)
May be something like below(Ex Loyal)
Count(
IF(Sum({Year={"$(=Max(Year))"}}Sales)>0 AND Sum({Year={"$(=Max(Year)-1)"}}Sales)>0, Customers)
)
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