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

# Aggregate Lookup Values

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

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:

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