Second aggregation not working as expected

[martynlloyd]

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.

[sudeepkm · Accepted solution]

now both the tables are linked based on the ClaimYear field.

In table one you do not have distinct ClaimYear as it was group by along with Chassis.

[#3]

Hey Martyn,

Would it be possible to attach an excel file with some dummy data and maybe whats the result you are getting vs expected. That would greatly help in addressing the issue.

Just an FYI,

Count(DISTINCT Chassis) as RolledUpChassisCount,

The above line in the first table will always be 1 as you are grouping by Chassis as well. So not sure why you are using that. Its equivalent to

1 as RolledUpChassisCount

Thanks

AJ

[martynlloyd]

Thanks, I didn't intend to link the tables.