Discussion Board for collaboration related to QlikView App Development.
In my application I have a table CLAIMFACTS, about 55,000 rows
To make statistics I aggregate this set of data with:
Count(DISTINCT Chassis) as RolledUpChassisCount,
Sum([DAF Claim- Total]) as RolledUpChassisCost,
Floor(Sum([DAF Claim- Total])/100) * 100 as RolledUpCostBucket
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:
Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount) as AverageYearlyCost,
Floor((Sum(RolledUpChassisCost)/Sum(RolledUpChassisCount))/100)*100 as AverageYearlyBucket,
Count(DISTINCT Chassis) as AverageYearlyFreq
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.
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
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,
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, I didn't intend to link the tables.