4 Replies Latest reply on Jun 13, 2017 6:26 AM by Stephen Gibbons

# Calculation Contexts - for each dimension (not displayed)

Hi. I need to design income bandings for individual clients. For example -

Client A - £130,000

Client B - £75,000

Client C - £300,000

Client D - £750,000

Client E - £30,000

Client F - £10,000

Client G - £400,000

I then need to use the following banding range (bold) as a dimension with the count of clients and sum of income for each band as 3 columns illustrated below (the Client dimension will be absent).

1. >£500,000 / 1 / £750,000

2. £250,000 to £500,000 / 2 / £700,000

3. £100,000 to £250,000 / 1 / £130,000

4. £50,000 to £100,000 / 1 / £75,000

5. <£50,000 / 2 / £40,000

The problem I am having is finding a function that calculates the income for each individual client rather than the sum of income for a given band, and then putting it in the correct band. If you take the above dataset, the sum of income from Client C and Client G would push them into the top bracket, but would be incorrect as the individual client income for those 2 clients is below £500,000.

(This can be done easily in BusinessObjects by using 'Foreach' in the formula - I need the equivalent in Tableau). Hope someone can help! Thanks.

(Apologies for not attaching a workbook - cannot do as company-sensitive data)

(Tableau 10.2)

• ###### 1. Re: Calculation Contexts - for each dimension (not displayed)

Stephen

look into LOD expressions of the form {fixed [client] : sum(income)}

Jim

• ###### 2. Re: Calculation Contexts - for each dimension (not displayed)

Stephen, this is tough without data but I think I can at least point you at the right places to look.

1) LOD calculations: you want a FIXED calc at the Client ID level: {FIXED [ClientID]:SUM([Income])}

2) Bins: usually when we want a histogram-like "banding" solution, bins are the answer.  It's possible that this is NOT the right answer here but I'd need to think about it more.

I'm going to create a really simple viz using the sample data you've provided and test both those theories!  Back shortly...

• ###### 3. Re: Calculation Contexts - for each dimension (not displayed)

Stephen, v 10.0 TWBX attached.

I had to create custom bins to get this to work but it should be pretty clear.  It's just a question of learning how to think the Tableau way (vs. BO) about solutions.

Oh, and it turns out we did NOT need the LOD calc here: the custom bin sizes partition the data correctly to only add up the right things.

Let me know if this isn't what you wanted and we can try other approaches.

• ###### 4. Re: Calculation Contexts - for each dimension (not displayed)

Thankyou both. I shall take a look and get back to you.