Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

Total Shown in Qlik does not match with excel total after exporting

Hi All,

I am facing a bit weird issue . I have a straight table and a measure like below

=Sum({<
[Expenditure Category]={'Labour'},
[Cost Status]={'TRC'},
[Archive Status]={'Archive'}
>}Amount).

when I export the straight table in excel, and take the sum of Amount from the excel rows , the total shown in Qlik does not match with the total shown in excel rows. There are around 30 dimensions(just for information)

Though the diff is very minor, but still it exists. Could it be a number format issue?

I have set the amount expression fixed to #,##0.00 (done from the Number tab)

Please help.

Thanks 

Ankhi

6 Replies
ramasaisaksoft

There may be the cause like

  • Your dimensions are "Supress when value is Null"
  • Check with current selection box so that you will see all Trigger selections/Activate sheet selections...etc.

check like this...

sunny_talwar

Could it be because of the rounding. Try to export an un-formatted chart and see if the sum matches or not.

Ankhi
Creator
Creator
Author

Hi Rama,

Thanks for the reply. However nothing is suppressed and no selection is done.

The total amount that is shown in Qlik is correct. But after downloading in excel when I am checking the total from excel rows its not matching the total in qlik. 

Regards

Ankhi

Ankhi
Creator
Creator
Author

Hi Sunny,

Thanks for getting back to me. I have updated my qvds and rounded of the amount to 5 decimal places. However the same thing is happening, Qlik total is correct but excel sum of rows do not match with Qlik total. Just for info there are around 138952 rows and the diff is around 1000 so for each row if take around 10p . My straight table formatting is upto 2 decimal places.  Just realized that if I set it two 5 decimal places , the sum of rows in excel matches with Qlik output. Seems that Excel is working with the absolute value..

 

Regards

Ankhi

nevilledhamsiri
Specialist
Specialist

May be if you bring in Aggr function the sum of total in the row must be shown as it should be. Add the sum(aggr(measure), with all chart dimensions.

Regards

Neville

 

Brett_Bleess
Former Employee
Former Employee

I would review the following Design Blog post, hopefully that may explain things for you:

https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.