Hi everyone, just thought I'd share this as a discussion.
Working with our (admittedly) bad data model (which I'm going to fix ASAP), I've noticed some strange behaviour with total calculation in tables. Taking this test data:
CustomerID:
load * inline [ CustomerID, MonthTotal
4, 40
];
CustomerValues:
load * inline
[ CustomerID, CustomerType, CustomerValue
4, 1, 40
4, 2, 40
4, 3, 40
];
Our users wanted a table by CustomerType, with the totals of CustomerValue and MonthTotal. (Sorry I couldn't think up a good analogy for this data model, but follow me).
Creating the table straight up in Qlik gives us this:
| CustomerType | Sum(MonthTotal) | Sum(CustomerValue) |
|---|
Totals | 40 | 120 |
| 1 | 40 | 40 |
| 2 | 40 | 40 |
| 3 | 40 | 40 |
Qlik has done the clever thing with "Auto" totals and assumed we wanted the total by CustomerID - but to the average user, they're going to point out that 40+40+40 does not equal 40. Note that this is using the "Auto" default total selection. If you change the total to "Sum" for MonthTotal, you get the (actual, database-join) sum of 120.
Now at this point, I know you're shouting at the screen going "Your data model is bad and you should feel bad", but surely we can improve this scenario by making more explicit options in the total calculation selection - in this case, if you assumed the sum around the join would work but didn't check the dropdown had defaulted to "Auto", you would be showing the incorrect figure. Perhaps make the title text default to "Auto(MonthTotal)", or add in explicitSum()/implicitSum() instead of just Sum()? DistinctSum()?
