Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
martynlloyd
Partner
Partner

Second aggregation not working as expected

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.

1 Solution

Accepted Solutions
sudeepkm
Specialist III
Specialist III

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.

View solution in original post

3 Replies
sudeepkm
Specialist III
Specialist III

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.

View solution in original post

Not applicable

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
Partner
Partner
Author

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