3 Replies Latest reply: Jul 18, 2014 10:44 AM by Martyn Lloyd RSS

    Second aggregation not working as expected

    Martyn Lloyd

      In my application I have a table CLAIMFACTS, about 55,000 rows

       

      To make statistics I aggregate this set of data with:

       

      RolledUpChassisCost:

      LOAD

      Chassis,

      ClaimYear,

      Count(DISTINCT Chassis) as RolledUpChassisCount,

      Sum([DAF Claim- Total]) as RolledUpChassisCost,

      Floor(Sum([DAF Claim- Total])/100) * 100 as RolledUpCostBucket

      Resident ClaimFacts

      Group By Chassis, ClaimYear;

       

      This works fine, and if I pivot this in Excel around the RolledUpCostBucket dimension I get a nice frequency histogram. Chassis frequency is in hundreds, which is correct.

       

      Then I try the pivot in next part of my script:

       

      Buckets:

      LOAD

      ClaimYear,

      Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount) as AverageYearlyCost,

      Floor((Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount))/100)*100 as AverageYearlyBucket,

      Count(DISTINCT Chassis) as AverageYearlyFreq

      Resident RolledUpChassisCost

      Group By ClaimYear;

       

      However, AverageYearlyFreq is now in tens of thousands (should be in hundreds), two developers cannot solve this.

       

      Sorry, I cannot upload qvw due to policy.

       

      Marty.