Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Max1984
Contributor III
Contributor III

Pivot table cumulative sum initial and current balance

Goodmorning everyone,

I have the following current situation (see image) where the initial balance "(1) Saldo iniziale" is calculated from the following formula that calculates the balance preceding the date of the calendar object (4):

(RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={"<$(=$(vInizioPC))"}, Anno=,Mese=,Giorno=>}[# Importo Fattura Iva]),0,RowNo(TOTAL)))
   -
  RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={"<$(=$(vInizioPC))"}, Anno=,Mese=,Giorno=>}[# Importo Pagato Scadenze Pagamenti]),0,RowNo(TOTAL))))

 while the current balance that takes into account the date range selected by the calendar object is calculated (4😞

(RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Fattura Iva]),0,RowNo(TOTAL)))
  -
 RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Pagato Scadenze Pagamenti]),0,RowNo(TOTAL))))

I made a pivot table (3) with 5 dimensions and 3 expressions; the expressions are:

1)"DARE"   
SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Fattura Iva])
2) "AVERE" 
SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Pagato Scadenze Pagamenti])
3) "SALDO"
(RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Fattura Iva]),0,RowNo(TOTAL)))
  -
 RangeSum(Above(TOTAL SUM({<[Master Calendar Date Calc]={">=$(=$(vInizioPC))<=$(=$(vFinePC))"}, Anno=,Mese=,Giorno=>}[# Importo Pagato Scadenze Pagamenti]),0,RowNo(TOTAL))))

situazione attuale.jpg

How can I start the cumulative sum of the "SALDO" by adding to each line of the pivot table the initial balance "Saldo Iniziale"?

thanks

11 Replies
sunny_talwar

Check attached

image.png

Max1984
Contributor III
Contributor III
Author

Very well,

thanks