topic DIfference between data in plain table and Pivot Table in QlikView

DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 15:20:24 GMT

Hi everybody !

I have a huge dataset, which I'm using for data presentation in various charts and tables.

Everything looks like working OK, but I noticed some small differences in data.

I filtered data in QV to the lovest level until I could localize the difference between expected data and data in QV.

I have a plain table with source data and the sum in rightmost column is correct (54 978 EUR).

I have a pivot table from the same data and sum is incorrect (51 111 EUR).

Both tables represent data from the same filtered dataset.

But when I remove the column "nazevob" from the plain table, it returns reduced number of rows and the sum equals the sum value in the pivot table:

How can I change the pivot table for getting correct data ?

Thanx in advance.

Duski.

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Tue, 14 Oct 2014 15:27:00 GMT

Dusan, it seems like maybe you're doing a "Sum of Rows" total instead of an "Expression Total" total (under the Expressions tab of your straight table). The issue is likely that "nazevob" is causing 2 rows to be returned sometimes, like the line that says -2,29 twice.

Vlad

Re: DIfference between data in plain table and Pivot Table

alexandros17 — Tue, 14 Oct 2014 15:28:25 GMT

Gave you Excluded null values for that column?

Re: DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 17:47:05 GMT

Thank you for you answer, Vlad.

You are right that in my straight table is "Expression Total" set to "Sum of Rows" - but the sum result in this table is correct! Also the double occurance of value -2,29 is correct - there are two records with this value in the source table; the only difference between this two records is value in field "nazevob".

Btw. the motive for "Sum of rows" setting came from solution of my previous problem, discussed here

SQL select - unique values / rows

Facit: my problem now is, how to achieve correct number of rows and sum value =54 978 EUR in my pivot table.

Thank you in advance.

Duski

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Tue, 14 Oct 2014 17:50:06 GMT

Ah, I see. Well, if you don't want to fix the fact table in your data model, you'll have to use aggr:

sum(aggr(

sum(whatever)

,all dimensions in your straight table list in comma-delimited format

))

Vlad

Re: DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 18:22:53 GMT

Thank you, Vlad.

Maybe is my expectation very simple and naive, but what I want in my pivot table, is plain sum in dimension "Čiastka" for all records in my dataset. All other filtering I can make through selections.

For your information: there are 26 fields in my dataset 😞

Could you suggest an exact syntax for my expression instead of currently used "sum(Čiastka)" for me ?

Thanks in advance.

Duski

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Tue, 14 Oct 2014 18:29:36 GMT

Right, try what I posted above:

sum(aggr(

sum(Čiastka)

,Rok,Obdobie,CISLOSTAVBY,all the other dimensions in your straight table that I can't type

))

Re: DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 19:45:08 GMT

Thank you, Vlad.

I used your expression and it solved most of my problems.

You helped me enormous, thank you very, very much !!!

But in one breath I'm a little bit sad, because I cannot completely understand your syntax 😞

For your information: I used syntax below, which contains all fields, which can have variety values.

sum(aggr(sum(Čiastka),CISLOSTAVBY,nazevob,Kód,Obdobie,Rok,Stav,Séria))

But in my QV I have a couple of a little bit more complicated charts, where your syntax doesn't work 😞

For instance another pivot table with calculated dimensions.

01. Orientačný plán

Current syntax:sum({$<Séria ={'01. Orientačný plán'}>}Čiastka)

Updated syntax: sum(aggr(sum({$<Séria ={'01. Orientačný plán'}>}Čiastka),CISLOSTAVBY,nazevob,Kód,Obdobie,Rok,Stav,Séria))

- doesn't work:

Any suggestions ?

Thanx in advance.

Duski

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Tue, 14 Oct 2014 19:52:33 GMT

Make sure that the aggr dimensions not only contain all the dimensions from your straight table, but also all the dimensions in your pivot table. For example, I think Projekt is missing.

Vlad

Re: DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 19:58:20 GMT

I added "Projekt", but no success.

Btw. CISLOSTAVBY and Projekt have the same relevance - CISLOSTAVBY is code for Projekt.

Duski

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Tue, 14 Oct 2014 20:06:24 GMT

So your aggr contains all dimensions now of your pivot table AND your straight table?

Re: DIfference between data in plain table and Pivot Table

Tue, 14 Oct 2014 20:13:12 GMT

Thank you Vlad.

Sorry, but I have to finish for today, because my baterry is nearly drained.:-(

But I think I'm on the way to understand what happens in the aggr statement.

Good night for today and thank you.

Duski

Re: DIfference between data in plain table and Pivot Table

Wed, 15 Oct 2014 06:27:06 GMT

Good morning, Vlad 🙂

Update: solved in this threat Table / Pivot table sum

One more question for helping me to better understanding how aggr works.

My motive is to find the simplest solution for maintenance my charts and pivot tables in the future.

Your suggestion to include ALL (26) fields into the aggr statement is probably correct, but it seems to me very uncomfortable for maintenance in future 😞

In one of my charts I have a calculated expression:

sum({$<Séria ={'08. Účtovníctvo'}>}Čiastka)

The goal is to sum all records from my dataset, where Séria="08. Účtovníctvo" following the current selection.

This works (with small issues with data, which we solved yesterday :-)) and it returns data even if Séria='08. Účtovníctvo' is not actually selected. This is the behaviour I want.

But when I extend the expression with aggr (to repair data, according to your yesterday help), it looks like this ...

sum(aggr(sum({$<Séria ={'01. Orientačný plán'}>}Čiastka),CISLOSTAVBY,nazevob,Kód,Obdobie,Rok,Stav,Séria))

... and it doesn't work; it returns dataONLY if Séria='08. Účtovníctvo' is actually selected. This is the behaviour I don't want. :.(

Any suggestion ?

Thanx in advance.

Duski

Re: DIfference between data in plain table and Pivot Table

vgutkovsky — Wed, 15 Oct 2014 14:49:33 GMT

Please don't double-post, it makes the community harder for everyone to use. I'm sure someone in the other thread can help you with this question.

Vlad