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

[petter]

You experience what Qlik calculate as "sum of expressions" and not as "sum of rows". Very often they overlap and are equal - sometimes they don't - as you  experience. You can make use of the aggregation helper function called Aggr() (named advanced aggregation although GroupBy() would be a better name in my opinion) so you can get the sum of rows instead of sum of expressions.

[jasmina_karcic]

How can I do that?

My expression is:

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

Thanks...

[petter]

The rule-of-thumb approach is to wrap the expression in an Aggr and then add the same dimensions as you have in the chart where you have the expression in the first place.

[jasmina_karcic]

Something like this?

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

[petter]

What are the dimension in the chart/table where your have your expression?

[jasmina_karcic]

I have 5 dimensions:

1. Number

2. Dim1

3. Dim2

3. Name

And 6 measures format as

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

(just different date I look)

[jasmina_karcic]

Dimensions:

1.Number

2. Dim1

3. Dim2

4. Name

5. Dim3

[jasmina_karcic]

This expression:

sum(aggr(fabs(sum({$<DATE={"$(=date(rangemin(today()-1,MAX(DATE))))"},  DIM={"XXXX"}>}VALUE)/1000), NUMBER,DIM1,NAME,DIM2,DIM3))

give me the same result as excel...

[jasmina_karcic]

But how two expressions give different result?

For example, this expression:

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

And this expression:

sum(aggr(fabs(sum({$<DATE={"$(=date(rangemin(today()-1,MAX(DATE))))"},  DIM={"XXXX"}>}VALUE)/1000), NUMBER,DIM1,NAME,DIM2,DIM3)) gives 43000

😕

how can I make a diagnose what is happening? 😕

Thanks