topic Re: TOTAL expression not counting correctly in QlikView

TOTAL expression not counting correctly

paulinhok14 — Tue, 05 Mar 2024 13:59:30 GMT

Hello dears!

I have an app in which I want to create a pie chart with relative classification, in %, for a specific field.

My field called "_Essentiality" has binary classification (0/1) and I want to count my Key field (%CompoundKey) for each of these classifications, having percentage for each class, example:

_Essentiality	Relative Count
0	97,3%
1	2,7%

But the problem is, when I try to use TOTAL expression in order to have my relative share, my TOTAL count doesn't match with the sum of Keys in each classification (see attached image):

As you can see, my count considering Dimension totals 49055 and my total count shows 49030, so when I try to create the A / B in order to have 0-100% sum, my "0" classification become, solely, more than 100%.

I've also tried to use ALL expression, without success.

Any ideas?

Re: TOTAL expression not counting correctly

JonnyPoole — Tue, 05 Mar 2024 14:08:29 GMT

The 'total' should be honouring the scope of the chart. My instinct is to check for a chart setting that could explain this, perhaps there is a 'dimension limit' in the chart? Or one of the dimensions has a 'hide nulls'. Something like that.

ALso, try creating a text box with just the expression placed in the text chart. If the value is 49055 you know its something specific about the pivot table chart.

Re: TOTAL expression not counting correctly

marcus_sommer — Tue, 05 Mar 2024 15:41:05 GMT

It looked that you are counting a key-field which often worked fine and personally I do it quite often. But remembering to quite old best practice guidance it shouldn't be done because not each side of the fact- and/or dimension-tables may have the same key-values (missing ones to all sides) and/or there might be any NULL's within them. If so the context of the view - object-dimensions, selections, advanced expressions and so on - could have an impact to the results.

Therefore you may try to enforce a certain view/relationship within the calculation, maybe by adding querying an table-unique field or something similar. More simple would be just to double the field within the target-table and then counting this.

Re: TOTAL expression not counting correctly

paulinhok14 — Tue, 05 Mar 2024 17:51:46 GMT

The text object try I've done it already. Shows 49030.

Regarding your comments on charts settings, I've done it all to show nulls, zeros, etc, etc. Nothing solved...

Re: TOTAL expression not counting correctly

paulinhok14 — Tue, 05 Mar 2024 17:54:35 GMT

Hey Marcus, thanks for your answer!

Even not figuring out why exactly this happens, I was able to solve using your insight on key-field count.

I've changed to:

count ( {<[QAS Ellegible] = {1}>} [QAS Ellegible] )

And seems to work right now. The total count column shows 49055.

I thought that because I've created a script field [QAS Elligible] and put into my set analysis expression, I would not have problem with key-field count.

So, even not knowing exactly why this happened, problem solved. Thank you!!!