Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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.
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.
Your file is too large. See here for how to prepare a sample:
Preparing examples for Upload - Reduction and Data Scrambling