Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dinicholls
Creator II
Creator II

Eh?! Pivot Table Total <> Straight Table Total

Hi All,

I'm at a bit of a loss here!

I have created a straight table, with various formulas and fields, and its adding up perfectly.

However, when I change the type to a pivot table, to group, it changes all the 'Grand' totals to a totally different figure, even though the lines within the table are showing the correct values. I've exported the pivot to excel, and added it up that way, so I know the 'Grand Total' is wrong.

Any thoughts on the problem?

Have attached JPG image to show problem

Thanks

Di

16 Replies
dinicholls
Creator II
Creator II
Author

Hi,

I don;t think people are understanding what I'm trying to say.

The problem I'm having is with the PIVOT table, NOT the straight table.

The pivot table is NOT adding up the calculated rows correctly like the straight table is. I won;t be using and other charts or tables, apart from what will be the one pivot table.

What I'm trying to understand is why the straight table will add the rows up correctly, based on the 'Sum of Rows' option, but, the PIVOT table, which is what I need to use, is NOT adding the calculated rows up correctly, even though in the 'Total Mode', the 'Sum of Rows' is selected.

Unfortunately, due to the company I work for, I am not allowed to give data out. I'm not sure though, even if I could gave this info out how it would change things? My data is right, my calculations are right, why are the totals in the two tables different?

If I flip the pivot table back to a straight table, it adds the calculated rows up correctly!

Di

kiranmanoharrode
Creator III
Creator III

Hi Diane,

Use AGGR in expression to Calculate correct grand Total,

For ex. I have 2 Dimensions in pivot table DIM1 and DIM2 and 1 measure Qty.

=AGGR(Sum(Qty),DIM1,DIM2)

Note: Include all dimension in AGGR function those are used in pivot table.

Regards,

Kiran

8976977897

Not applicable

Hi Diane,

the point is that the pivot total is the Expression total and not the total of all the rows in the chart, I've tried to show the difference below.

Example:

Fact Table:

%KeyFieldProduct, Value

1     ,     10

2     ,     20

3     ,     30

Dimension Table:

%KeyFieldProduct, ProductName

1     ,     A

1     ,     a

2     ,     B

3     ,     C

Simple example but if you use ProductName as a dimension in a straight table and use sum of rows (with an expression of Sum(Value)), the result will

A 10

a 10

B 20

C 30

Total 70

Where as in a pivot table, the total will give you the expression total, in this example 60, because that is the absolute sum of the Value field, regardless of the duplication issues caused by the product field

To get the two charts aligned I would need to join in the product field to the fact table and duplicate up values (not recommended) or clean up my product field to remove the duplication

Hope that helps

Joe

dinicholls
Creator II
Creator II
Author

Hi All,

Have tried the 'AGGR' function, as Joe suggested. Works out the calculations for each row, but I don't get a total in my pivot table.

Have attached JPEG.

Thanks

Di

dinicholls
Creator II
Creator II
Author

Hi All,

Sorted thank you!

Used the AGGR function, but didn't add the 'SUM' before it!

Thank you!!!

Di

master_student
Creator III
Creator III

Hi, could you share a print screnn with us of your table and expression

Thanks

master_student
Creator III
Creator III

When I used the

=Aggr(Sum({< $(vSetView)>}POKTND),Famille, CDR, LB, CODE_PROJET, [Month of date],PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_ACHETEUR, [Nom du Projet], PO_COMMENTS )

vSetView to select YTD/mtd

My  result table is :

Capture.PNG

I don't know why I didn't see all months displayed (because I am slecting  YTD and month = June , the total does not exist