topic Re: Eh?! Pivot Table Total <> Straight Table Total in QlikView

Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Wed, 07 Jan 2015 11:28:33 GMT

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

Re: Eh?! Pivot Table Total <> Straight Table Total

Wed, 07 Jan 2015 11:34:10 GMT

Hi Diane,

you have probably selected the 'sum of rows' within the straight table, where as the total in the pivot is the expression total. These two things can give different results depending on your data model.

For instance if you have a dimension table with multiple values, linking to a single row in your fact table and use this dimension in the charts, the sum of rows will duplicate up the value (count all the duplicate rows), where as the expression total will not (it will still show the duplicate rows in the pivot chart, but won't affect the total)

hope that helps

Joe

Re: Eh?! Pivot Table Total <> Straight Table Total

alexantone — Wed, 07 Jan 2015 11:45:08 GMT

Hello Diane

Can you tell me the expression used for the colmun 'CASH TARGET'?

is there null value?

Re: Eh?! Pivot Table Total <> Straight Table Total

jonathandienst — Wed, 07 Jan 2015 11:59:32 GMT

Referring to Joe Simmons' post on sum of rows:

It may not be the data model that causes a problem. Expressions with ratios will also return different results in a pivot to a straight table with sum of rows enabled. The sum of set of ratios (sum of rows) is arithmetically not the same as the ratio of the sums (expression total).

HTH

Jonathan

Re: Eh?! Pivot Table Total <> Straight Table Total

Wed, 07 Jan 2015 12:11:54 GMT

thanks Jonathan, I just went with the one example that came to mind but yes true there are other potentials such as this good point

Re: Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Thu, 08 Jan 2015 11:05:24 GMT

Hi All,

There won't be any nulls, but there are 0's. I've made sure on purpose!

Cash Target expression:

if([T8 Check]='Yes', Sum([Line Debt]),(Sum([Line Debt])/100)*$(CashTarget))

The '$(CashTarget)' is an variable based on an input box.

Thanks

Re: Eh?! Pivot Table Total <> Straight Table Total

ashwanin — Thu, 08 Jan 2015 11:44:14 GMT

Strange it should not be like this.

Can you check the Dimension Limit in Straight Table. Is there any limitation put by you for expression of Cash Target? If yes, then remove the same and check the answer.

Re: Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Thu, 08 Jan 2015 13:34:20 GMT

Hi,

Nothing is ticked in regards to dimension limits in regards to straight table.

Have added jpeg showing the expressions of the two tables.

Its really confusing me as to why its happening. All the data and the formulas are correct in both tables, its just the totals!

Arghhhhhhh!

Re: Eh?! Pivot Table Total <> Straight Table Total

Thu, 08 Jan 2015 13:41:58 GMT

Hi Diane,

Please see my earlier post about sum of rows, looks like this is your issue. If you adjust the straight table to be expression totals the charts should match for you.

As for the difference between the two, this will likely be down to a data issue as I have said

hope that helps

Joe

Re: Eh?! Pivot Table Total <> Straight Table Total

kavita25 — Thu, 08 Jan 2015 13:50:11 GMT

Hi,

Post a sample data with your logic.

Regards,

Kavita

Re: Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Thu, 08 Jan 2015 14:20:05 GMT

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!

Re: Eh?! Pivot Table Total <> Straight Table Total

kiranmanoharrode — Thu, 08 Jan 2015 14:32:49 GMT

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

Re: Eh?! Pivot Table Total <> Straight Table Total

Thu, 08 Jan 2015 15:24:17 GMT

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

Re: Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Thu, 08 Jan 2015 15:58:09 GMT

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

Re: Eh?! Pivot Table Total <> Straight Table Total

dinicholls — Thu, 08 Jan 2015 16:48:51 GMT

Hi All,

Sorted thank you!

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

Thank you!!!

Re: Eh?! Pivot Table Total <> Straight Table Total

master_student — Mon, 20 Jun 2016 14:11:00 GMT

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

Thanks

Re: Eh?! Pivot Table Total <> Straight Table Total

master_student — Mon, 20 Jun 2016 14:23:09 GMT

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 :

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