1 Reply Latest reply on Apr 3, 2017 12:22 PM by Yuriy Fal

    Aggregate Lookup Values

    gerald.chee

      Good afternoon everybody,

       

      I am current having an issue with the following error: Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated.

       

      Basically I have 2 inputs [Holding Units] and [Buy Units]. I created 2 calculated fields on top of each other, which are

       

      def [TotalWeeks]: As long as either categories are positive, then display 1. I have no issue with this, they can be summed in the grand total

      if [Buy Units] > 0

      then 1

      elseif [Holding Units] > 0

      then 1

      ELSE null

      END

       

      and

       

      def [AdjTotalWeeks]: If [TotalWeeks] is true i.e. = 1, and I have an empty cell between 2 cells with  [TotalWeeks] = 1 and the previous value is not 0, then return 1 for that cell.

      if AVG([TotalWeeks]) = 1

      then 1

      ELSEIF not isnull(lookup(AVG([Holding Units]),-1)) and not isnull(lookup(AVG([Holding Units]),1)) and lookup(avg([Holding Units]),-1) > 0

      then 1

      else null

      END

       

      To illustrate things, supposed I have 2 data for [Holding Units] with each time Ti.

      T1T2T3T4T5
      530nil6
      531nil6

       

      From [AdjTotalWeeks], my output would be

      Grand TotalT1T2T3T4T5
      411101
      511111

       

      The problem is after I have the output for T1 to T5, the grand total is still reflected as 1 instead of adding across the rows. When I tried to use sum[AdjTotalWeeks], i received the error that it is already an aggregated amount so I cannot aggregate it further. I tried using window_sum, avg, min, max but they do not change the problem. Is there a way to go around this?

       

      Thanks,

      Gerald

        • 1. Re: Aggregate Lookup Values
          Yuriy Fal

          Hi Gerald,

           

          The problem you're trying to solve

          (not getting the result you want

          using the particular pills layout) is twofold.

           

          First, the [AdjTotalWeeks] calculation

          draws 1s on the additional densified Marks --

          your data has no rows for that particular

          combination of the dimension values on a view.

           

          So one opt to use Table Calculations to draw this Marks,

          and it should be another (nested) Table Calculation

          to sum up the numbers across these Marks.

           

          In your case it would be as simple as that:

          WINDOW_SUM([AdjTotalWeeks])

          Compute using --> your_time_dimension

          (those with Tn values on Columns).

           

          But here comes the second complication:

          Grand Total calculations (Tableau-generated ones)

          don't take into account densified Marks on a view.

          And there is no way (as far as know) to make them aware.

           

          So even if one have the "right" calculation for totals,

          the result could not be placed in the (Grand) Total cell.

           

          The solution? A sort of -- to combine the desired "table" layout on a dash.

           

          Please find the attached version 10.0 workbook as an example.

          Hope it could help understand the problem better.

           

          Yours,

          Yuri

          2 of 2 people found this helpful