1 Reply Latest reply on Mar 11, 2017 4:47 AM by Vasil Petkov

# Displaying at a higher level to LOD calculation

I'm trying to create a worksheet that will segment subcategories (into High/Med/Low) based on the proportion that the subcategory makes up of the category.

To do this I'm using the below IF statement to create a dimension (Sub Cat Segment) to segment into high/low:

if

(

{ FIXED [Order Date], [Category], [Sub-Category]: sum([Number of Records])}

/

{ EXCLUDE [Sub-Category]: sum([Number of Records])}) > .4 then 'High'

ELSE

'Low'

END

Firstly, the calc created shows as measure, which is strange given the above calc returns only "high" or "low".
If I convert using ATTR function in the shelf it returns incorrect data (all showing as low, even though I know that one should be in high).

Thanks in advance if you can provide any guidance on this.

Andrew

• ###### 1. Re: Displaying at a higher level to LOD calculation

Hi Andrew,

You don't really need a the FIXED in your calculation  Tableau will aggregate a simple sum on the same since your view is at that level. You really only need 2 calcs:

Category Total:

{ EXCLUDE [Sub-Category]: sum([Number of Records])}

and

Sub Cat Segment:

if
sum([Number of Records])
/
SUM([Category Total]) > .4 then 'High'
ELSE
'Low'
END

Keep in mind that this will only work because the data allows it, meaning if a Sub Category goes from more than 40% to less, lets say 30, next year your segment will show * since you will be trying to show two values at the same time. The only way to break those into 2 lines will be to do the segment calc using FIXED calculations.

Hope this helps.

Best,

-V