topic Re: Expression total vs Sum total in QlikView

Expression total vs Sum total

Fri, 23 Sep 2011 12:47:39 GMT

Hi,

Can someone explain how my expression total can differ from the Sum total, as the expression is a Sum([value])

Thanks.

Re: Expression total vs Sum total

its_anandrjs — Fri, 23 Sep 2011 12:50:20 GMT

Hi,

Suppose you have value

Item A,B,C

Value =10,20,30

Sum(Total Value ) = 60 It will give 60 value

but

Sum( Val ) gives 10 , 20 ,30 for

A,B, C

See attachment file

Let me know

Rgds

Anand

Expression total vs Sum total

Fri, 23 Sep 2011 12:54:20 GMT

How does the expression total behave then? If the expression is Sum([value])

I am just interested to know how does this differ from the sum total?

Thanks

Expression total vs Sum total

SunilChauhan — Fri, 23 Sep 2011 13:00:23 GMT

i have seen expression total is sometimetimes not showing exact value.

but if u cantake sum of total row then it will show sum of all row.

as per me we ca trust on sum of rows

thanks

Sunil chauhan

Re: Expression total vs Sum total

its_anandrjs — Fri, 23 Sep 2011 13:00:33 GMT

Hi,

Expression total behaves like it id for your expresson only.

If you simple Sum( value ) it gives 60 but if you take any dimension like item

For

A = 10

B = 20

C = 30

It give Sum( value ) according to dimension

But Sum(Total value) gives only all sum value it is use full for show percentage values.

See attached sample

Rgds

Anand

Expression total vs Sum total

Fri, 23 Sep 2011 13:11:05 GMT

Hi,

My problem is with my pivot table. It shows when i had expressio total in level 1 58, 000e,

but when you expanded it and sent it to excel the total equals 62,000e??

Now ive change the expression total mode to Sum of rows, and now it displays the totals correctly.

Is it a glitch, or?

Sorry, i cant post the file as it's our company finance.

Re: Expression total vs Sum total

its_anandrjs — Fri, 23 Sep 2011 13:14:42 GMT

Hi,

Yes right it is the difference when you use it on the pivot table or straight table where it Count, Sum, Average your rows according to your data.

If you want to use Total sum so for this use variables store Sum(Total value) there and then use it on charts where ever you want.

Let me know

Rgds

Anand

Expression total vs Sum total

Jason_Michaelides — Fri, 23 Sep 2011 13:21:22 GMT

It's not a bug. Expression total evaluates the expression over the whole data set (subject to selections) which may or may not equal the sum of the rows.

For example:

SaleID,Item,SaleValue

1,1,10

2,1,50

3,2,30

4,1,20

5,2,100

6,3,42

7,3,60

8,2,25

9,1,60

10,3,30

Now, say you have a chart with Item as the dimension and Avg(SaleValue) as the expression. You will have:

Item Average

1 35

2 51.67

3 44

The average of all sales calculated by Avg(SaleValue) will be 42.7 and this is what is shown by Expression Total.

However the Sum of Rows will be 130.67.

The Average of Rows will be 43.56 - different to Avg(SaleValue).

Hope that makes it clearer.

Jason

Expression total vs Sum total

SunilChauhan — Fri, 23 Sep 2011 13:25:00 GMT

i have seen expression total is sometimetimes not showing exact value.

but if u cantake sum of total row then it will show sum of all row.

we can trust on sum of rows

thanks

Sunil chauhan

Expression total vs Sum total

Fri, 23 Sep 2011 13:39:49 GMT

Hi,

Thanks for the explanation, however, i went into the raw data and the total for Q1 is the total displayed in the expression total.

So now the total sum is incorrect?? how did that happen? dont even understand how its possible.

Expression total vs Sum total

Jason_Michaelides — Fri, 23 Sep 2011 13:42:21 GMT

What are the dimensions of the pivot table? Can you post an image of the data model?

Expression total vs Sum total

Fri, 23 Sep 2011 13:49:00 GMT

Hi,

I attached an excel file to show the issue..

Re: Expression total vs Sum total

Jason_Michaelides — Fri, 23 Sep 2011 13:52:42 GMT

And the Data Model? As a general rule you should not perform calculations on key fields as you have done here (N-2). You can get unpredictable results...

Expression total vs Sum total

Fri, 23 Sep 2011 14:01:44 GMT

Expression total vs Sum total

its_anandrjs — Fri, 23 Sep 2011 14:02:17 GMT

Hi,

I think there is aggregation need according to the dimensions where you are need to sum the values then it will show correct value.

Rgds

Anand

Re: Expression total vs Sum total

Jason_Michaelides — Fri, 23 Sep 2011 14:09:39 GMT

The relationship between N-2 and Quarter has to cross the PO table. Is there definitely an association for very record? I think you may need to work on your data model a bit first. Which table is the fact table? Looks like the PO table is, as there is a date, but Value is on tab1-1. If you improve the model you may find the results tally with what's expected.

Re: Expression total vs Sum total

Fri, 23 Sep 2011 14:25:01 GMT

Hi Jason,

And first of all thanks for all the help.. The issue was with my data model.

Thanks everyone

Expression total vs Sum total

Jason_Michaelides — Fri, 23 Sep 2011 14:33:36 GMT

Glad you got it sorted. Please be sure to mark helpful/correct answers for others to see.

Expression total vs Sum total

its_anandrjs — Fri, 23 Sep 2011 14:35:59 GMT

Hi,

If it seems you the data model not work well try to remove unnecesary field from the tables or make a child table if necesary on the model and make a primary keys to get data from the child tables. Also when ever you check that the table is not more use try to map that table.

Rgds

Anand