New Customers throughout the years

[#1]

Hi,

I'm trying to create a bar chart (stacked) to compare Customers behavior (new, kept and lost) through the time (Years, Months, etc).

Due to my dimension is "Year", I'm facing problems using Set Analysis, because it only performs the calculation one time per object not by row. I already have a "workaround" that give me the correct numbers, but I would like to know a better solution. This is my current expression:

=Pick(Match(Year,2011,2012,2013,2014,2015),

Count( {$<CustomerID=p({$<Year={2011},Sales={'>0'}>} CustomerID)*e({$<Year={2010},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2012},Sales={'>0'}>} CustomerID)*e({$<Year={2011},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2013},Sales={'>0'}>} CustomerID)*e({$<Year={2012},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2014},Sales={'>0'}>} CustomerID)*e({$<Year={2013},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2015},Sales={'>0'}>} CustomerID)*e({$<Year={2014},Sales={'>0'}>} CustomerID)>} distinct  CustomerID)

)

Find my New Customers_demo.QVW example attached.

* I also tried using Alternate States and AsOf Tables, but I couldn't make them work.

** I'm not considering doing aggregations in the script at this moment because the data model is more complex, and I would like to make this analysis using other dimensions.

Regards,

[hvfalcao]

Hi Octavio,

With some adaptations I was able to make it work, because I'm loading with SQL... but the rest is just perfect...

The only problem now is that I need to separate, NEW from RETURNING clients... Do you have any idea?

New - just the first year that the client bought.

Returning - when the client has already bought some year, dint bought for a year, a returned on the next year...

I really appreciated your help! Thank you!

[#3]

Hi Henrique,

Maybe you can use a Mapping Table and and Apply map or following the same scripting. Something like:

  if(Peek(CustomerID, -2) = CustomerID and Peek(Sales?,-2) = 1 and Sales? = 1 and Peek(Sales?) = 0, 1, null()) as R

[hvfalcao]

Octavio,

I found the solution to my case. Please check this link if you want: Re: New/Lost/Returning/Loyal Customers

Thank you!