New/lost customers as a dimension

[leenlart]

Hello!

I need to develop a table that will show new and lost customers as a dimension, with multiple expressions in the columns.  New customers are those that have purchased in the current year (or selected period), and lost customers are those that purchased in the same period last year but not this year.  

I have the correct set analysis expressions to get the volume for new and lost customers, for example, but the users would like to add some other indicators.  Instead of having two columns for each indicator (new and lost) I'd like to create a dimension with "new customers" and "lost customers".  

Something that might look a bit like this : 

Is it possible to add a calculated dimension for this ?  I haven't found anything like this in the multitude of posts on new/lost customers!

My expression for new client volume looks like this : 

sum( {< TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'},%CLIENT_ID = {"=sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU])>0 and sum({<TYPE_CALENDRIER={'N-1'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU])=0 "} >} [TONNAGE VENDU])

And my expression for lost client volume looks like this : 

sum( {< TYPE_CALENDRIER={'N-1'},ORIGIN={'FACTURES'},%CLIENT_ID = {"=sum({<TYPE_CALENDRIER={'N-1'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU])>0 and sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU])=0 "} >} [TONNAGE VENDU])
 

Thanks for any help or ideas.  Feel free to shoot me down if this is just not a good idea!

[Vikash]

Hi,

First define the two expressions in two variables. Like- 

vNewCustomer and vLostCustomer

The Use the below expression in the Calculated Dimesion-

=aggr(if($(vNewCustomer )<>0,'New Customers',
aggr(if($(vLostCustomer)<>0,'Lost Customers'), Dimension)),Dimension)

Hopefully you will be able to see the desired result.

VK