topic Re: Bizzarre in QlikView

Bizzarre

jduenyas — Wed, 25 Nov 2020 16:16:04 GMT

Did anyone see anything like this happening?

Total on columns are incorrect!!! 6+4+2+3 = 15 and not 14. And so are the other columns. (I am not concerned about the last column as it is correct.

Re: Bizzarre

sunny_talwar — Thu, 11 Aug 2016 23:57:47 GMT

Are you using if statement or some sort of complex expressions? Try using:

Sum(Aggr(YourExpressions, YourPivotTableDimensions))

This should solve your issue

Re: Bizzarre

jduenyas — Fri, 12 Aug 2016 00:15:49 GMT

Thanks.

I have done that to no avail. Will have to investigate some more unless someone has a quick solution

Re: Bizzarre

sunny_talwar — Fri, 12 Aug 2016 00:18:13 GMT

That has always worked for me without failure. Would you be able to post a screenshot of your expression?

Re: Bizzarre

sunny_talwar — Fri, 12 Aug 2016 00:19:17 GMT

And possibly a screenshot of your dimensions

Re: Bizzarre

Anonymous — Fri, 12 Aug 2016 07:47:43 GMT

Hi Josh,

Are you counting distinct? Could you share your expression?

Regards!

Re: Bizzarre

squeakie_pig — Fri, 12 Aug 2016 08:35:55 GMT

Check the Total Mode on the Expression tab.

It might be set to something other than the Expression total. You can't change it in pivot table mode, but change back to straight table and change it back to be what you need or the sum

Re: Bizzarre

ThornOfCrowns — Fri, 12 Aug 2016 08:46:07 GMT

Check for DISTICT / NODISTINCT values in your sum. I have found a QlikView-ism that sometimes discards identical rows from the sum.

Re: Bizzarre

alexandros17 — Fri, 12 Aug 2016 08:52:02 GMT

Convert the table into a straight table and see what happens:

The values should remain the same.

Now, in the expression tab select Sum of Rows instead of expression total.

What happens now? Are totals correct?

If the answer is yes then you are using expression with if conditions.

The only way to solve the problem is to aggregare every single line in order to let the total work correctly

Let me know

Re: Bizzarre

Colin-Albert — Fri, 12 Aug 2016 09:14:56 GMT

Are the figures you are summing integers?

How are the values displayed if you set the number format to 4 decimal places?

Remember a pivot table does not sum the rows to get the total, but recalculates the expression ignoring the dimension at the total level.

To get the sum of rows you need to use sum(aggr(expression)) .

This post illustrates how totalling works in pivot tables Pivot Table: Sum of Rows? | Qlik Community

Re: Bizzarre

jduenyas — Fri, 12 Aug 2016 19:39:50 GMT

Thanks to all that replied.

Alessandro's suggestion to convert to straight table helped identify my difficulties and it had to do with confusions in calculating some on a UTC date/time v Local Date/Time.

Issues resolved