Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
leenlart
Creator
Creator

New/lost customers as a dimension

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 : 

leenlart_0-1622801321663.png

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!

Labels (1)
1 Reply
Vikash
Contributor III
Contributor III

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