0

I have two categorical columns "Job Industry Categories" and "Wealth Segment" and I can create a crosstab using "Job Industry Categories" in rows and "Wealth Segment" to count values for each industry.

This is how my crosstab looks like currently:

Pic 0

But I want to dynamically count wealth segment column for each industry and then return the wealth segment with maximum count for each industry.

This is what I want to acheive:

Pic 1

What I have tried:

I have tried using LOD expression to get the max of count of wealth segment for each industry but that returns a non-aggregated value and I am not able to extract the wealth segment label for the corresponding value.

MAX(
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) }
)

I have also tried using IF THEN statement with LOD Expression but, since LOD expression is a non-aggregated value, it throws error. This is what I tried:

IF 
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) } =  MAX(
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) })
THEN
[Wealth Segment]
END

ERROR: cannot mix aggregate and non-aggregate with this function

Thanks in advance.

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
Kaushlendra Singh
  • 15
  • 5
  • Can you please explain why wealth segment for agriculture ind is affluent despite count of high net worth is greater? Even high networth count is greater than affluent in all Industries except manufacturing?? – AnilGoyal Dec 26 '20 at 06:14

1 Answers1

1

You are doing 2-3 things not in the correct way here.

1 Your first screenshot (table) is not your data, but already a crosstab query (called pivot table in excel) so how can you imagine some extra column either in row or in column. (second screenshot). tableau is not a spreadsheet like excel, where you can create rows/columns as per your convenience.

2 Why have you used count(wealth status) as aggregation. Though it will give same values but here you required count of records/data called as number of records in tableau's earlier versions.

3 You have not considered the situation where both counts are equal. (these can very well be equal, can't they?)

If you want you can create a new column altogether (without a cross tab view/viz of course) where you can get name of wealth category having more records than other.

I created some random data to replicate your problem. My data looks like (there are 100 rows) (table name is weal.csv)

enter image description here

Cross-tab looks like

enter image description here

Create a calculated field say Max of Wealth with the following calculation

IF {Fixed [Sector], [Wealth Status]: COUNT([weal.csv])} =
{FIXED [Sector]: MAX({Fixed [Sector], [Wealth Status]: COUNT([weal.csv])})}
then [Wealth Status] END

You can filter out null values from this field and can get desired viz as

enter image description here

Note two results in health sector

My sincere advice to new tableau users - unlearn spreadsheet first

Good luck

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
  • 1
    Worked like charm, since most of my data have non-null values so I wrapped your LOD in { FIXED [Sector]: Max(.....LOD expression....) } and then I don't have to manually filter null values. – Kaushlendra Singh Dec 27 '20 at 22:30