New, Lost and Returning Customers

Hi Nikos 

Yes it can be done with some calculations.

One thing you need to decide is about the timefilter. How far do you want to go back?

Also - it could actually be 2 different visualizations - one showing the 3 possible different types of status in a crosstab, and the other just showing the numbers.

First one would have months on the horizontal axis and customers on the vertical - and the other visualization the other way around.

I did the first one - a fragment of the end result shown here:

Quick guide to do this:

Create 3 calculations (all as new measure) that will set a "flag" (value=1) where the customer meets the requirements:

New Customer calculation syntax:
if sum(0, 0, m1) > 0 and sum(d1:0, 0, m1) = sum(0, 0, m1) then 1 else 0

Explained: If the current cell (current column and row) holds a value - and that value is equal to the sum of all the columns from the first up to the current then we have a new customer

Lost Customer calculation syntax:
if sum(-1:0, 0, m1) = 0 and sum(-2:0, 0, m1) > 0 and sum(d1:0, 0, m1) > 0 then 1 else 0

Explained: If the previous column and current column of the current row holds no values and it's not already marked as lost and the value of all the columns > 0 (meaning there has a value at some point), then we have a lost customer

Returning customer calculation syntax:
if sum(-2:-1, 0, m1, 0) = 0 and sum(0, 0, m2) = 0 and sum(0, 0, m1) > 0 then 1 else 0

Explained: If the 2 previous columns didn't have any numbers (which means the customer is lost) and the flag for new customer is 0 (we don't want to confuse a returning customer with a new one) and the current cell in the current column and row has a value, then we have a returning customer.

Now just hide the original measure using visibility.

Hide all values that are 0 in the the 3 calculations using visibility agents.

Use number formatting to  change the numbers to text with this "hack"

Finally make a color agent for each of your flags with the condition value=1

Hope it makes sense

And here's the other one - showing a count New, Lost and Returning customers per month.

First end result:

Now the same 3 calculations need to be made (only the column and row references are switch around because the to axis' have been switched.

New Customer: if sum(0, 0, m1) > 0 and sum(0, d1:0, m1) = sum(0, 0, m1) then 1 else 0

Lost Customer: if sum(0, -1:0, m1) = 0 and sum(0, -2:0, m1) > 0 and sum(0, d1:0, m1) > 0 then 1 else 0

Returning Customer: if sum(0, -2:-1, m1, 0) = 0 and sum(0, 0, m2) = 0 and sum(0, d1:0, m1) > 0 and sum(0, 0, m1) > 0 then 1 else 0

We also need 3 calculated columns to count all the flags for each month:

No of New Customers: sum(all, 0, m2)

Explanation: sums of all columns if the current row for measure 2 (which is our first calculated measure - New Customer)

No of Lost Customers: sum(all, 0, m3)

Explanation: sums up measure 3

No of Returning Customers: sum(all, 0, m4)

Explanation: sums up measure 4

Now hide everything using visibility (Except your 3 calculated columns)

Hope it makes sense

Hi Niels,

Thank you very much for the provided pattern and "hack".

It works great except from the fact that totals for Lost and Returning Customers do not return any results.

Do you have any idea why does this happen and how we could solve this?

Ahh I see it. It's a bit of long explanation, but in short TARGIT will try to do the calculation on all levels (subtotal and grandtotal level and leaf level). This will create a problem when you refer to -1 or -2 since that will mean the previous subtotal/grandtotal (which isn't there)

Anyway - the fix is to:

1. rename your Lost Customers calculation to Intermediate Lost Customers and also...
2. rename your Returning Customers calculation to Intermediate Returning Customers

Now add a new calculated column called Lost Customers with the syntax:

if allcount(c2,all(c),m1)=0 then sum(c2,0,m1) else sum(c2,all(c),m1)

Explanation:
C2 is the name of the second calculated columns (Intermediate Lost Customers)
The allcount checks for "children" which is a way of detecting if you are on subtotal level. If that is the case the number of children (c) should be greater than 0. Then we sum up all the rows to create a new correct subtotal

Also add a new calculated column with the name Returning Customers with the syntax:
if allcount(c3, all(c), m1) = 0 then sum(c3, 0, m1) else sum(c3, all(c), m1)

Explanation: See above

Now hide the intermediates, and you should have subtotals that are correct.:

Niels after making a slight modification to the formula, since I hide the days in dates hierarchy your fix worked great.

Thank you very much for your time and explanations!

Great to hear that it worked!