Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gloria_bertini
Contributor

Problem with cumulative sum in pivot table multiple dimension

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.

 

1 Solution

Accepted Solutions

Re: Problem with cumulative sum in pivot table multiple dimension

You will have to work with Dimensionality() function to get this working.... can't tell you the exact expression without playing around with it....

2 Replies

Re: Problem with cumulative sum in pivot table multiple dimension

You will have to work with Dimensionality() function to get this working.... can't tell you the exact expression without playing around with it....

gloria_bertini
Contributor

Re: Problem with cumulative sum in pivot table multiple dimension

Thank you very mutch.

Now i try

Community Browser