Partial sum total not matching excel sum of rows

[Anonymous]

Hi Everyone,

I am new to QlikView, please help to resolve this issue.

I have a Pivot Chart Table in which it has 15 Dimensions and in expression formula Sum(Aggr(Sum)) formula.

Partial Sum is set to first dimension and after export if I check partial sum total does not match with sum of excel rows.

Sum of excel rows is showing correct total.

Have updated a simple QlikView file,where need to display every company , ref and expression.

If you export the table in excel and look at the Sum of expression its different from QV partial sum.

Need excel sum to be shown in QV partial sum.

Any help or comment is appreciated.

Message was edited by: Ajit Deshpande

[swuehl]

Have you used all 15 chart dimensions as dimensions to the aggr() function?

If yes, could you post some more details about the difference?

[Anonymous]

Hi ,

Thanks for reply.

No , have used only 2 dimension to the aggr() function.

Formula looks like:

=if(

  round(

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1   and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  )<>0,

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

sum(Aggr(sum(if( Condition1   and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1   and  Condition2 , Value3)),Dimension1,Dimension2))

,null()

)

Now if export the pivot table in excel and check the sum in excel it is correct but my partial sum on pivot does not show correct value

[swuehl]

Pivot table partial sums are evaluated as expression total by default.

Are you expecting to get a sum-of-rows?

You can emulate a sum-of-rows total in a pivot table using advanced aggregation (there is a chapter in the HELP that describes this).Something like

=If(Dimensionality() = 0, // check if you need to adapt this to another dimensionality

Sum( Aggr(

if(

  round(

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1  and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  )<>0,

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

sum(Aggr(sum(if( Condition1  and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1  and  Condition2 , Value3)),Dimension1,Dimension2))

,null()

)

,Dimension1)),

// ELSE

if(

  round(

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1  and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  )<>0,

  sum(Aggr(sum(if( Condition1  ,Value1/ExchangeRate)),Dimension1,Dimension2))

  -

sum(Aggr(sum(if( Condition1  and  Condition2 , Value2/ExchangeRate)),Dimension1,Dimension2))

  -

  sum(Aggr(sum(if( Condition1  and  Condition2 , Value3)),Dimension1,Dimension2))

,null()

)

)

I assume there should be better ways to get your requested results, but without knowing your data (model), full chart context and requirements, it's mostly guessing.