Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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()?
I must admit I haven't fully understood what you are trying to achieve here.
Just in case you want to show th sum-of-rows for Month Total independent of the total option, you could calculate your sum-of-rows using advanced aggregation (like you would need for a pivot table):
=Sum( Aggr( Sum(MonthTotal), CustomerType ))
Probably you are asking for something else. I understood you are not asking for a data model change, but then...?
Sorry for my poor explanation - I wanted to explore the problem I've noticed with the Auto/Sum totals above. I noticed this with our data and setup a test case for this post. I have fixed the problem with my data but thought it was an interesting case to put up here.
It looks like it is being explored here anyway:
bug or expected behavior of QV version 12 SR1
From my understanding of SQL-style joins, this result (however correct for the data model) is unintuitive, and I wanted to highlight it as such