Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Have you used all 15 chart dimensions as dimensions to the aggr() function?
If yes, could you post some more details about the difference?
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
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.