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

    Displaying at a higher level to LOD calculation

    Andrew Murphy

      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
          Vasil Petkov

          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