topic Re: Issue with straight table total in QlikView

Issue with straight table total

Sat, 30 Jun 2012 14:31:00 GMT

Hi All,

I have an straight table in which I have two two expressions for which i am showing the Total value on the top row.I see the Total as 775.5$ but when I export the excel and sum up the values I get 765.4$.. What could be the reason for this??

Any suggestions for this issue are appreciated..

Thanks

Re: Issue with straight table total

swuehl — Sat, 30 Jun 2012 15:32:56 GMT

Please change the total mode of your expression in the expression tab from 'Expression Total' to 'sum of rows' and check if this makes a difference.

Re: Issue with straight table total

Sat, 30 Jun 2012 15:41:53 GMT

Dear Sir,

What is the difference between Expression Total & Sum of Rows ?

Does sum of rows has any affect in Pivot table ?

I've read one post in another thread http://community.qlik.com/message/216590

"The difference in sum of rows and expression typically happens when you have something like a ratio (as in your example), that is not additive. For example, consider two data points: 1/3 and 2/3

When added together you will get 1/2+2/3 = 1, but when calculated with the expression, you will get (1+2)/(3+3) = 1/2. The expression is mathematically "correct", and is not arithmetically the same as the sum of rows."

I'm nt able to understand this explanation

Please throw some light on this

Regards

Re: Issue with straight table total

swuehl — Sat, 30 Jun 2012 16:07:35 GMT

If you have a data model like

TEST2:

LOAD * INLINE [

Dim, Link

A, 1

A, 2

B, 2

B, 3

];

LOAD * INLINE [

Link, TestValue

1, 1

2, 2

3, 3

];

and you create a straight table chart with dimension Dim and expression sum(TestValue), you'll see the difference.

The expression total will sum up the TestValues in global context, like in a text object, without using the dimension to group the results by. So it's just looking at the second table, and sum TestValue over the three records, this will return 6.

In your table rows, the sums are to be calculated in the context of the dimension, so we need to follow Link to get the TestValues per Dim. This will give us 3 and 5 for the dimension values A and B. The sum of rows is 8.

You can get a similar issue also with a single table

LOAD * INLINE [

Dim, TestValue, TestValue2

A, 1, 2

B, 2, 2

C, 3, 2

];

and calculating as expression sum(TestValue) / sum(TestValue2)

The expression total will return 1 (six divided by six), while the sum of rows is 0.5 + 1 + 1.5 = 2.

The expression total will not sum up the single rows, it will evaluate the expression in total or global context (no dimensions taken into account). If you need a sum of the rows, you'll need to change the total mode (for a pivot table, you can't change the total mode in the expression tab, but there is a workaround using advanced aggregation, look into using sum-of-rows in pivot tables using advanced aggregation in the Help).

Hope this helps,

Stefan

Re: Issue with straight table total

Sat, 30 Jun 2012 16:25:51 GMT

The toal which is showing up when i set the total to expression total is correct i mean 775.5 but why do i get 765.5 when i sum up in the excel??

Re: Issue with straight table total

swuehl — Sat, 30 Jun 2012 16:36:03 GMT

So you are getting the same results for expression total and sum-of-rows total mode in your QV table, right?

And how you checked that 775.5 is the 'correct' answer?

Are you maybe using some kind of rounding, i.e. is the precision of numbers different in your exported excel file than in the QV app?

Could you post a small sample app that demonstrate your issue?

Re: Issue with straight table total

Sat, 30 Jun 2012 17:34:12 GMT

If I put the same expression in text object it gives me 775.5 which is corect.. but why does that give 765.5 when i use the same in the straight table.. actually the data model is bit tough and due to security issues i could nt post d example

Re: Issue with straight table total

swuehl — Sun, 01 Jul 2012 16:23:38 GMT

>...but why does that give 765.5 when i use the same in the straight table.

You mean 765.5 as sum of rows in Excel? or do you see the value 765.5 now also in the straight table? I must admit I am a bit confused.

Could you post the dimensions / expression you are using in the straight table? Are you using alternate states / dimension limits or some other non-default setting?

Could you at least try to explain your data model, as far as relevant to your chart object?

It is really difficult to explain why you see some differences between Excel and your chart object with this little details given.

Regards,

Stefan

Re: Issue with straight table total

Mon, 02 Jul 2012 05:49:48 GMT

Hi Stefan,

I see 765.5 as total when i export the data to excel and sum up the values.. but when i select the expression totla in the chart it shows me 775.5 as total at the same tome when i chnage the expression total to sum of rows it gives me 765.5. I am attaching the excel in which you could see the total mismatch.Please let me know if you need any info.. In the current estimate column i use set analysis in order to create it..

Thank u so much for spending your time in order to help me with this issue..

Regards

Lavanya

Re: Issue with straight table total

swuehl — Mon, 02 Jul 2012 10:42:19 GMT

Ok, so you do see a difference between total modes sum-of-rows and expression total. And sum in excel equals the sum-of-rows. Is this correct?

If so, I would try focusing on the difference between sum-of-rows and expression total, and not looking at excel files, since this is just adding more confusion.

I assume that your issue is due to your data model (in combination with the expression used), so without knowing any more details, I can't help you here.