Sum of rows in Pivot table of Qlikview

[#1]

Hello,

I am using the below expression to calculate Volume in Straight table and it works fine and sum of rows is also works fine. total is 99,109

if (sum(Quantity_Billed_KG)> 0,

FirstSortedValue(distinct Aggr(

sum({<Valid_From_Date_Master = {"<=$(vEndDate)"},Valid_To_Date_Master = {">=$(vEndDate)"}>} Quantity_Billed_KG)

,Sold_To_Customer_Code,Material_Code,End_Use_Customer_Code,Customer_Group_Code,TX_Currency_Code,Volume_Tier,Created_On_Date),-Created_On_Date)

,sum(Quantity_Billed_KG))

But the same expression in Pivot table doesn't work. some rows are getting eliminated. total is being displayed as 89,110

=sum(distinct Aggr(if (sum(Quantity_Billed_KG)> 0,

FirstSortedValue(distinct Aggr(

sum({<Valid_From_Date_Master = {"<=$(vEndDate)"},Valid_To_Date_Master = {">=$(vEndDate)"}>} Quantity_Billed_KG)

,Sold_To_Customer_Code,Material_Code,Volume_Tier,TX_Currency_Code,End_Use_Customer_Code,Customer_Group_Code,Created_On_Date),-Created_On_Date)

,sum(Quantity_Billed_KG))

,Sold_To_Customer_Code,Material_Code,Volume_Tier,TX_Currency_Code,End_Use_Customer_Code,Customer_Group_Code))

[jonathandienst]

Firstly, the Aggr() needs to include all the pivot table dimensions in its ownl list of dimensions.

To get a sum of rows, wrap the entire expression in another Aggr() with all the pivot table dimensions.Something like:

Sum(Aggr(

If(Sum(Quantity_Billed_KG) > 0,

  FirstSortedValue(distinct Aggr(

  sum({<Valid_From_Date_Master = {"<=$(vEndDate)"},Valid_To_Date_Master = {">=$(vEndDate)"}>} Quantity_Billed_KG),

  Sold_To_Customer_Code, Material_Code, End_Use_Customer_Code, Customer_Group_Code, TX_Currency_Code, Volume_Tier, Created_On_Date <make sure this list includes all the table dimension>),

  -Created_On_Date)

  ,sum(Quantity_Billed_KG)

), <all your chart dimensions here> )

Add the dimensions as indicated.

[#3]

Please find attached Report. Summary tab displays volume = 0 for Material Code 767924. where as it should display volume as 10,000 against Material Code 767924. which is correct in details tab.

[jonathandienst]

Your file is too large. See here for how to prepare a sample:

Preparing examples for Upload - Reduction and Data Scrambling