topic Re: Sum Mismatch Between Qlikview and Excel in QlikView

Sum Mismatch Between Qlikview and Excel

pnn44794 — Sat, 12 Oct 2019 22:56:30 GMT

Hello:

I have a dataset where within a Qlikview straight table, I get a Sum of 295. When I export the data to Excel, I have a Sum of 303. When I manually check the export, I also come up with 303 for a total. I would appreciate any help in resolving the discrepancy.

As always, any and all help is appreciated.

Re: Sum Mismatch Between Qlikview and Excel

tamilarasu — Mon, 29 Jan 2018 05:06:16 GMT

Hi Perry,

For me it's showing 303 in the straight table. Could you please attach sample qvw file.

Re: Sum Mismatch Between Qlikview and Excel

Siva_Sankar — Mon, 29 Jan 2018 08:10:35 GMT

Hi Perry,

I too getting 303 for Product as dimension. Can you check whether you checked Sum of Rows instead of Expression Total or not?

Re: Sum Mismatch Between Qlikview and Excel

jonathandienst — Mon, 29 Jan 2018 08:39:27 GMT

It could also be that you have checked "Suppress Nulls" on a dimension.

Re: Sum Mismatch Between Qlikview and Excel

prieper — Mon, 29 Jan 2018 09:14:47 GMT

Following Jonathan's idea, it makes sense to create a straight table with just this expression - no dimensions.

Then the result should be as expected. Adding dimensions then should not change the result.

Re: Sum Mismatch Between Qlikview and Excel

vvvvvvizard — Mon, 29 Jan 2018 09:25:13 GMT

in the expression tab , change from expression total to sum of rows or vice versa

Re: Sum Mismatch Between Qlikview and Excel

prieper — Mon, 29 Jan 2018 10:15:58 GMT

With this formula and the underlying data, result should be the same, when choosing sum of rows, or expression total as there is just a simple filtercondition applied.

Re: Sum Mismatch Between Qlikview and Excel

PrashantSangle — Mon, 29 Jan 2018 10:24:26 GMT

Try below,

sum(Aggr(YourCurrentExpression,all_dimension_present_in_your_current_chart))

Regards

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 15:29:21 GMT

Hello Sivasankar:

Thank you for your reply. I do have Sum of Rows checked.

Re: Sum Mismatch Between Qlikview and Excel

prieper — Mon, 29 Jan 2018 15:34:21 GMT

What does your datamodel look like?

Do you have a duplication of data caused by duplicated entries in another table?

Re: Sum Mismatch Between Qlikview and Excel

Siva_Sankar — Mon, 29 Jan 2018 15:59:01 GMT

Could you share your application? or share the screenshot?

-Siva

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:27:40 GMT

Hello Sivasankar:

I could, but it would take me a little bit of time to prepare it for sharing. However, I believe I've solved the issue. Please see my response to Peter Rieper.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:28:24 GMT

Hello Tamil:

I could, but it would take me a little bit of time to prepare it for sharing. However, I believe I've solved the issue. Please see my response to @Peter Rieper@.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:33:07 GMT

Hello Jonathan:

Thanks for the reply. Suppress Null values is not checked. I believe I've resolved the problem after reading Peter's last response to me. Please see my response to Peter.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:34:37 GMT

Hello Yusuf:

Thanks for the reply. I did both with no luck. However, I believe the problem is resolved. Please see my response to @Peter Rieper.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:36:17 GMT

Hello Peter:

I tried that but with no luck. However, you're other reply which I'll respond to, made me look at my datamodel and what I was using in the calculation.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:38:37 GMT

Hello Prashant:

thanks for the reply. I have not tried this yet, though I believe I have a solution. Please see my response to @Peter Rieper.

Re: Sum Mismatch Between Qlikview and Excel

pnn44794 — Mon, 29 Jan 2018 17:54:20 GMT

Hello Peter:

You've pointed me in the right direction! I do have multiple tables that are being loaded and apparently the two tables in play with this are not a 100% match (join). I was using Approval Complete Year from one table and PROD_KEY from another table.

Once I used just one of the tables and changed PROD_KEY to a different column from the same table that Approval Complete Year is in, I got the results I was expecting, 303. I had to change the expression to a Count versus a Sum, but I get the right results.

Thank you very much!!!