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

    How to count number of records in an aggregation calculation?

    christian.winebrenner

      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?