2 Replies Latest reply: Aug 3, 2017 11:47 AM by Gloria Bertini RSS

    Problem with cumulative sum in pivot table multiple dimension

    Gloria Bertini

      Hi everybody ,

      First of all sorry for my English.

      I have a problem with cumulative sum in a pivot table with three dimension: MeseAnno2 (Month Year), DataScadenza (Expiration DateRagioneSociale (Business Name) Stato (Condition, a control field that show me if the incoming/ outflow are sure or not) and one measure: Saldo (Differece between income and outflow )

      I tried two different espression:

       

      Aggr(RangeSum( Above( total  

       

         Sum({<FlagCF = {'1'} ,TipoImporto={'S'}, DataScadenza = {">=$(=vMaxData) <=$(= MonthEnd(AddMonths(vMaxData,6)))"} >}ImportoValutaNazionale)  -Sum ({<FlagCF = {'2'}, TipoImporto={'O'}, DataScadenza = {">=$(=vMaxData) <=$(= MonthEnd(AddMonths(vMaxData,6)))"} >} ValoreConsegneProgrammate)  - Sum({<FlagCF = {'2'}, TipoImporto={'S'}   , DataScadenza = {">=$(=vMaxData) <=$(= MonthEnd(AddMonths(vMaxData,6)))"} >}ImportoValutaNazionale) + Sum ({<FlagCF = {'1'},TipoImporto={'O'}, DataScadenza = {">=$(=vMaxData) <=$(= MonthEnd(AddMonths(vMaxData,6)))"}  >} ValoreOrdinato) + Sum({<DataScadenza = {"=$(vMaxData)"}, SegnoSaldoFinale = {"D"}, MeseAnno2=   >}SaldoFinale)- Sum({<DataScadenza = {"=$(vMaxData)"}, SegnoSaldoFinale = {"A"} ,MeseAnno2= >}SaldoFinale)                     

       

      ,0,RowNo(Total))) , MeseAnno2, DataScadenza,RagioneSociale,Stato)

       

      That show the correct cumulative sum for the every single table’s rows, but don’t show total’s values for MonthYear dimension nor the total cumulative sum, they are all null.

      The Second expression:

       

      Sum(Aggr( RangeSum( Above( total

         Sum({<FlagCF = {'1'} ,TipoImporto={'S'}, DataScadenza = {">=$(=vMaxData) <=$(=vData6S)"} ,RagioneSociale = >}ImportoValutaNazionale)  -Sum ({<FlagCF = {'2'}, TipoImporto={'O'}, DataScadenza = {">=$(=vMaxData) <=$(=vData6S)"},RagioneSociale= >} ValoreConsegneProgrammate)  - Sum({<FlagCF = {'2'}, TipoImporto={'S'}   , DataScadenza = {">=$(=vMaxData) <=$(=vData6S)"} , RagioneSociale= >}ImportoValutaNazionale) + Sum ({<FlagCF = {'1'},TipoImporto={'O'}, DataScadenza = {">=$(=vMaxData) <=$(=vData6S)"},RagioneSociale= >} ValoreOrdinato) + Sum({<DataScadenza = {"=$(vMaxData)"}, SegnoSaldoFinale = {"D"}  , MeseAnno2= >}SaldoFinale)- Sum({<DataScadenza = {"=$(vMaxData)"}, SegnoSaldoFinale = {"A"} , MeseAnno2= >}SaldoFinale)  

      ,0,RowNo(total))),MeseAnno2))

       

      That show correct total’s values for MonthYear dimension but it show 0 for partial cumulative sum in single table row. Also the total cumulative sum is wrong.