9 Replies Latest reply on Jul 2, 2018 8:05 AM by Simon Runc

# Count number of clients new, lost and existing

Hi all,

I have a simple table with customers in the rows and two years in the columns summarizing sales by customer. (see attached example) I have been able to add a calculated fields which is colouring the lost, new and existing clients:

IF ZN(LOOKUP(SUM([Amount\$]),-1)) = 0 and LAST() = 0

THEN

'New'

ELSEIF ZN(LOOKUP(SUM([Amount\$]),1)) = 0 and LAST() = 1 THEN

'Lost'

ELSE

'Existing'

END

Now I want a separate sheet counting the number of new, lost and existing customers across the same periods. How can I do that?

• ###### 1. Re: Count number of clients new, lost and existing

hi Carlo,

So if you use the New, Lost, Existing Calculation we used here

rather than your Table Calculation version, it will create a genuine row level dimension, that you can use in other sheets/vizes, without needing customer or year in the VizLoD.

There is a way using a variation on your calculation, where we keep Customer and Year in the vizLoD, but it's quite complicated and you'll constantly be fighting the fact that your CalculationLoD (the level at which you need the calculation to operate, being Customer/Year) and the displayLoD (the level that you want the chart to display at, being customer segment/cohort) are different.

• ###### 2. Re: Count number of clients new, lost and existing

Hi Simon,

it is giving weird results with my data so I am trying to do it in the file you submitted. I have obtained this:

but doesn't look correct.

Consider also that I may need to compare not full year figures (e.g. Q1 FY2017 vs Q1 FY2018). Not sure the calculated fields would work in the same way?

• ###### 3. Re: Count number of clients new, lost and existing

So you'll need to ensure that you are only looking at 2 years, and that the 2 years are in a context filter (attached example).

In your Quarterly situation...would that mean that a customer would be existing if they shopped in each year in that quarter? or would you assess them if they spent at any point in both years? or something else?

• ###### 4. Re: Count number of clients new, lost and existing

Hi Simon,

don't know why but with my data your example doesn't work and I cannot understand why. The calculation of New, Lost and Existing customer (your Customer Group calculated field) produces wrong assignments. What can be wrong? Unfortunately I cannot send you my data..

• ###### 5. Re: Count number of clients new, lost and existing

Please see the results I get (I have narrowed down customer names). Hope you can better understand:

• ###### 6. Re: Count number of clients new, lost and existing

hi Carlo,

So my first guess, is that you just need to make the Year Filter a "context filter". You can do this by clicking on the year pill in the filter and selecting "add to context".

As we've use FIXED LoDs to pick up the Max Year, and FIXED LoDs are computed before any filters are applied, it will be taking FY 2019 as the Max year. By adding a filter to be a "context filter", it bumps it up the calculation pipeline, so that it is applied before the LoDs are computed.

hope that does the trick

1 of 1 people found this helpful
• ###### 7. Re: Count number of clients new, lost and existing

Hi SImon,

I did try (I did not know how to make a context filter). However does not seem this applied major changes:

Looks like the calculated field Cli_NL_LOD generates wrong assumptions... Getting crazy here!

• ###### 8. Re: Count number of clients new, lost and existing

Hi Simon, I discovered the issue !!!

It is all about our FY which start Dec 1 and ends Nov 11. The concept of FY is embedded in the Tableau configuration but clearly your formula did not consider it and always returned with 3 (calendar) years in scope !!

Now I have created a new calculated field called FY and all works !!

Thanks again!

Carlo

• ###### 9. Re: Count number of clients new, lost and existing

Good work...yes the Non-January starting year in Tableau doesn't always behave how you'd expect globally in a model!!

1 of 1 people found this helpful