3 Replies Latest reply on Jul 8, 2013 5:28 AM by Jonathan Drummey

# How to count number of records in an aggregation calculation?

I have some data that needs aggregated on a weighted basis, which I have working - the relevant data looks something like this:

CategoryACategoryBDimension1
Value
Weight1Weight2

Plants

GreenABC511.1
PlantsGreenABC611.1
AnimalsMammalXYZ101.8
AnimalsMammalXYZ112.8
AnimalsReptileXYZ81.9

CategoryA->CategoryB are a Hierarchy. I'm putting the expanded hierarchy (CategoryA then CategoryB) on columns, then Dimension1 followed by the formula/aggregation calculation on rows (it shows up as AGG(CalculationName).

I don't think it matters, but the aggregation calculation is SUM(Value*Weight1*Weight2)/SUM(Weight1*Weight2), and I'm getting the value that's expected.

A business rule says that if there are less than X rows of data, the result of the calculation should not be shown (0, NULL, whatever). I've tried using the following to get an idea whether or not it was counting, and no matter how many rows are being using in the SUM calculation, I'm always getting 0.

IF ([Number of Records] >= X) THEN

SUM(Value*Weight1*Weight2)/SUM(Weight1*Weight2)

ELSE

0

END

How can I count the number of records that Tableau is using to do an aggregation calculation?

• ###### 1. Re: How to count number of records in an aggregation calculation?

Use SUM([Number of Records]) >=X in your IF statement. (That will get rid of the aggregate/non-aggregate error, and should be an accurate calc based on how I'm reading your description).

• ###### 2. Re: How to count number of records in an aggregation calculation?

Thank you, Jonathan - this seems to do what I needed.

• ###### 3. Re: How to count number of records in an aggregation calculation?

You're welcome!