Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Auto table totals inconsistent with data model

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:

CustomerTypeSum(MonthTotal)Sum(CustomerValue)

Totals

40120
14040
24040
340

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()?

2 Replies
Highlighted
MVP
MVP

Re: Auto table totals inconsistent with data model

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...?

Highlighted
Contributor III
Contributor III

Re: Auto table totals inconsistent with data model

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