Export data shows different sum than in app

[jasmina_karcic]

Hi experts,

When I export data from one application, and make sum of one column, it is different than sum in qlik sense app...

What is problem?

Thanks

Jasmina

[MK9885]

Can you provide a sample app?

As far as I know, Qlik export reports AS IS...

Sometimes you may have to convert your excel data to 'Numbers'

Since Qlik has already calculated your expression for all the rows, when you export it the XL would do the same, just sum the rows like Qlik.

No expressions are applied in XL after export.

[jasmina_karcic]

When I export it is already in number format..

[jasmina_karcic]

When I export data it is already in number format...

[MK9885]

share an app if you can?

[jasmina_karcic]

no, I cannot

[juraj_misina]

The difference is that by default Qlik uses so called "expression totals" mode, which means that engine performs exactly the same calculation with the same data set, but ignoring all dimensions. So in your case it first calculates

Sum({$<DATE={"$(=date(rangemin(today()-1,MAX(DATE))))"},  DIM={"XXXX"}>}VALUE)/(1000)

and only then applies fabs(). So it sums up all individual DATA rows defined by your selection and set analysis, and then applies fabs() function. Again, this ignores dimensions, so this is a simple sum of all data rows.

The second expression with Aggr() (and this is basically the same you do in excel) it first calculates the fabs(sum()) on each individual TABLE row defined by aggr/table dimensions and only then sums those numbers up. So in this case you have only positive numbers on all table rows and you sum those.

Hope this helps.

Juraj

[juraj_misina]

By the way, you can change this behavior by adjusting "Totals function" setting for the measure in table properties.

[MK9885]

try without fabs in your expression?

[jasmina_karcic]

This is good thinking, when I change this fabs() I got sum/total, as I need 21000. But again in export data if I make sum of this column I got 42 900.

[petter]

The short answer is: "The difference is that you have one vs two levels of aggregation"

For the longer answer I want to refer to other excellent answers on this topic on this Community:

Sum of Rows and Expression Total Difference

QlikView Technical Brief - AGGR