DIfference between data in plain table and Pivot Table

[#1]

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.

[vgutkovsky]

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

[alexandros17]

Gave you Excluded null values for that column?

[#4]

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

[vgutkovsky]

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

[#6]

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

[vgutkovsky]

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

))

[#8]

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

[vgutkovsky]

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

[#10]

I added "Projekt", but no success.

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

Duski