8 Replies Latest reply on Jan 30, 2017 8:37 AM by Andrew Whittam

# Using Indices to compare profiles

Hi, I am an absolute newbie with Tableau! Impressed so far but have hit a brick wall.

I have an Age profile of people buying product A, B and C.

I want to understand if, by product, if the customers are more/less likely to have certain age profiles. To do this in xl I simply create indices...

The indices which are how the column % of a products Age group compares to the total for that age band for all products.

From the index we can understand if that age group is over/under (>100 or < 100) represented for the product.

 Age Customer Count Col % Index A B C Total A B C Total A B C Under 18 677 294 378 1,349 2.6% 0.9% 1.7% 1.7% 153% 56% 98% 19-25 7,278 7,697 5,955 20,930 27.8% 24.8% 26.1% 26.1% 106% 95% 100% 26-40 11,275 9,756 7,721 28,752 43.0% 31.4% 33.8% 35.9% 120% 88% 94% 40-60 4,341 2,530 1,743 8,614 16.6% 8.1% 7.6% 10.8% 154% 76% 71% 61-75 1,107 860 872 2,839 4.2% 2.8% 3.8% 3.5% 119% 78% 108% 76+ 5,471 2,319 1,657 9,447 20.9% 7.5% 7.3% 11.8% 177% 63% 62% Total 30,149 23,456 18,326 71,931 115.1% 75.5% 80.3% 89.8% 128% 84% 89%

Does anyone know how I can do this in Tableau?

Thanks

Andrew

Insight Analyst

London Borough of Bexley

• ###### 1. Re: Using Indices to compare profiles

Hi.

Can you post xls.

I couldn't understanding your calculations.

None of printed values summarize 100%.

• ###### 2. Re: Using Indices to compare profiles

Hi Luciano, I fixed the %'s and file is attached.

Thanks

Andrew

• ###### 3. Re: Using Indices to compare profiles

Andrew, this is hard to do.

Maybe there is another solution but i'm nor getting even close.

My problem here is that %1 break through customers.

I need total for calculation.

I only used this of your xls:

If you can give me this pivoted and repeating % 1 Totals by customer, i can calculate numerator and use % 1 Totals by customer you informed as AVG to calculate your Index column.

1 of 1 people found this helpful
• ###### 4. Re: Using Indices to compare profiles

Hi Luciano, Sorry to take so long to come back to you.

Am not sure where the "%1" comes from, what I was after was "index". I use it to compare groups to a universe.

In this case we are comparing age profiles for prod A, B and C. If we take the original counts table and calculate the % of each age band is of the total for the group.

This is shown in grid in the middle part of the above (totals corrected to 100%). This is an intermediate step and that section of the grid isn't usually displayed in the output.

The index is simply the % for each age group - for that product - against the universe % for the age group,

In general where the result is >1 then the product is overrepresented is that age group for that product, <1 = lower. This is shown in the right hand side of the grid, and in indices.xlsx attachment. I format as % as it's easier to digest.

Any idea any one?

Thanks

• ###### 5. Re: Using Indices to compare profiles

No idea because you need total to use in calculation.

David Li, this is a real challenge.

I can't see it right now. Can you?

• ###### 6. Re: Using Indices to compare profiles

Hi Andrew and Luciano! Sorry about the wait. We can do this entirely with LOD calculations. Please see the attached workbook (9.3+).

I did the calculations step-by-step in different calculated fields so you can see what's going on, but the final product simplifies to:

[Number] * {SUM([Number])} / ({FIXED [Customer]: SUM([Number])} * {FIXED [Age]: SUM([Number])})

For the data source, I basically did the same thing Luciano did and unpivoted the values you put in the Excel, but this should still work if you have multiple records per Customer x Age.

• ###### 7. Re: Using Indices to compare profiles

Thanks, am on leave now but will check out on return.

Andrew

• ###### 8. Re: Using Indices to compare profiles

Thanks for all your help with this. My colleague has made this all work. I can't post the final version as it uses our real data. Her comment was that this is much more difficult than in Excel!

Cheers

Andrew