4 Replies Latest reply on Dec 12, 2016 2:47 AM by Timothé Le Vigouroux

# How to create calculated field for determining first 30% of client and second 30% of client according to turnover?

Hi,

I would need help on implementing the following on Tableau.

I have a sales transaction table with Client and turnover linked to a table with Sales representatives.

All Sales Representatives are have a different portfollio (the amount of client assigned to a Sales Rep. can vary).

I would need to make a calculated field in order to find out the amount of turnover for the top tiers of client and second tier of client for past year.

For example: Sales rep. Mr Pink has following clients

Client 1: 500 EUR last year

Client 2: 450 EUR last year

Client 3: 300 EUR last year

Client 4: 200 EUR last year

Client 5: 100 EUR last year

Client 6: 10 EUR last year

Client 7: 9 EUR last year

Client 8: 3 EUR last year

Client 9: 3 EUR last year

Client 10: 1 EUR last year

I'd like to have 2 calculated fields:

First tier = 300 (then I know that up to 300eur included, client of Mr Pink are in the top tier)

Second tier= 10 (then I know that up to 10eur included, client of Mr Pink are in the second tier)

Of course it would need to be dynamic according to the Sales Rep., past year need also to be dynamic. I assume for the year part I should use something like (year(TODAY())-1)

I intend to use later on these calculated fields for more complex calculations.

Thanks for you help,

Timothe

• ###### 1. Re: How to create calculated field for determining first 30% of client and second 30% of client according to turnover?

Hi Timothe,

Find my approach as reference below and stored in attached workbook version 9.3

TIer:

if {fixed [Sales Rep],[Customer]:sum([Sales])}>=300 then "First Tier"

elseif {fixed [Sales Rep],[Customer]:sum([Sales])}>=10 then "Second Tier"

else "Last Tier" END

• ###### 2. Re: How to create calculated field for determining first 30% of client and second 30% of client according to turnover?

Thanks for your answer but unfortunately my issue is that I need to know the value of first tier and second tier for any of my sales rep.

These tiers can vary depending of Sales rep. (number of customer and turnover are different for sales rep and consequently we will have diffrent value of first and second tiers).

For example:

Sales rep. Mr Pink has following clients

Client 1: 500 EUR last year

Client 2: 450 EUR last year

Client 3: 300 EUR last year

Client 4: 200 EUR last year

Client 5: 100 EUR last year

Client 6: 10 EUR last year

Client 7: 9 EUR last year

Client 8: 3 EUR last year

Client 9: 3 EUR last year

Client 10: 1 EUR last year

By filtering on Mr Pink we would have calculated fields:

First tier (will be the top 3 clients)  = 300

Second tie (will be from rank 4 to rank 6)r= 10

Sales rep. Mr Black has following clients

Client 11: 1000 EUR last year

Client 12: 50 EUR last year

Client 13: 30 EUR last year

Client 14: 25 EUR last year

Client 15: 21 EUR last year

Client 16: 19 EUR last year

By filtering on Mr Black we would have calculated fields:

First tier (will be the top 2 clients)  = 50

Second tier (will be from rank 3 to rank 4) = 25

Any ideas?

• ###### 3. Re: How to create calculated field for determining first 30% of client and second 30% of client according to turnover?

Timothe,

Could you attach packaged workbook.

That's way faster to investigate the issue.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: How to create calculated field for determining first 30% of client and second 30% of client according to turnover?

Thanks Shinichiro san,

I have then created an new discussion with my enclosed file (with dummy data) as I did slightly change my problem so it can fit my dummy datas.