Pivot table cumulative sum initial and current balance

[Max1984]

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))))

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

[sunny_talwar]

First thing I would say is to attach the sample to this new post and also please provide the expected output based on the selections you have made. I think you provide us expected output, but we don't really know what selections you have made to get to that output. Please mention the selections made as well so that we don't have to do trial and error to see how you are getting to the numbers.

Best,
Sunny

[Max1984]

Hi Sunny,
my tables with the relations are shown in the figure:

the  table "Fatture_Passive" is created by the concatenation of two loaded qvd files

Fatture_Passive:
LOAD Distinct 
 [Fatture Passive Admin.URL]	       as  [% Id Fatture Passive], 
 [Fatture Passive Admin.NumeroFattura] as  [Numero Fattura Passiva], 
 Num(Floor(Date#([Fatture Passive Admin.DataFattura],'dd/MM/yyyy hh:mm:ss')))		             as  [Master Calendar Date Calc],
Date(Date#([Fatture Passive Admin.DataFattura],'dd/MM/yyyy hh:mm:ss'),'dd/MM/yyyy')		     as  [Data Fattura],
Date(Date#([Fatture Passive Admin.DataFattura],'dd/MM/yyyy hh:mm:ss'),'dd/MM/yyyy')		     as  [Data Reg],
[Fatture Passive Admin.Fornitore.NAME]	 as [Nome Fornitore Fatture Passive], 
[Fatture Passive Admin.Fornitore.URL] 	as [% Id Fornitore],
[Fatture Passive Admin.Categoria.NAME]	as [Nome Categorie Fatture Passive], 
[Fatture Passive Admin.Categoria.URL] 	as [% Id Categorie], 
Num#([Fatture Passive Admin.TotaleImporto])	as [# Totale importo], 
Num#([Fatture Passive Admin.Squadre.Importo])	as [# Imponibile Squadra], 
[Fatture Passive Admin.Squadre.Squadra.NAME] 	as [Nome Squadra Fattura Passiva], 
[Fatture Passive Admin.Squadre.Squadra.URL] as [% Id Squadra/Subappaltatori]  
FROM
qvd\fatture.qvd
(qvd);
Concatenate(Fatture_Passive)
ScadenzePagamenti:
LOAD Distinct 
ElencoReportScadenzeA.URL                 as [% Id Scadenze Pagamenti], 
Num#(ElencoReportScadenzeA.Importo)       as [# Importo Scadenze Pagamenti], 
Num(Floor(Date#([ElencoReportScadenzeA.DataScadenza],'dd/MM/yyyy hh:mm:ss')))             as  [% Master Calendar Date Num], 
ElencoReportScadenzeA.Mezzo          as [Mezzo pagamento Scadenze Pagamenti],  
ElencoReportScadenzeA.Descrizione    as [Descrizione Scadenze Pagamenti],   
Num(Floor(Date#([ElencoReportScadenzeA.DataPagamento],'dd/MM/yyyy hh:mm:ss')))		             as  [Master Calendar Date Calc],
Date(Date#([ElencoReportScadenzeA.DataPagamento],'dd/MM/yyyy hh:mm:ss'),'dd/MM/yyyy')		as [Data Reg],
 ' '		as  [Data Fattura],
Num#(ElencoReportScadenzeA.ImportoPagato) as [# Importo Pagato Scadenze Pagamenti], 
ElencoReportScadenzeA.NumeroFattura       as [Numero Fattura Passiva], 
ElencoReportScadenzeA.Fattura.URL         as [% Id Fatture Passive], 
ElencoReportScadenzeA.Fornitore.NAME   as [Nome Fornitore Fatture Passive], 
ElencoReportScadenzeA.Fornitore.URL       as [% Id Fornitore]    
FROM
qvd\ScadenzePagamenti.qvd
(qvd);

 The calendar is created by the following script:

sub MasterCalendar

TRACE 'Loading master calendar';

tmp_minmaxdate:
load
    MIN([% Master Calendar Date Num]) as MinDate,
	MAX([% Master Calendar Date Num]) as MaxDate
resident Fatture_Passive;


LET vDateMin = peek('MinDate',0,'tmp_minmaxdate');
LET vDateMax = peek('MaxDate',0,'tmp_minmaxdate');
LET vDateToday = Num(Today());  

DROP TABLE tmp_minmaxdate;

TempCalendar:
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
$(vDateMin) + RowNo() - 1 AS TempDate  
AUTOGENERATE 1  
WHILE $(vDateMin) +IterNo()-1 <= $(vDateMax);  

Calendar:
LOAD
       Num(TempDate) as [% Master Calendar Date Num],
       Date(TempDate) as [Data Esatta],
       Month(TempDate) as Mese,
       MonthName(TempDate) as AnnoMese,
       Year(TempDate) as Anno,
       Day(TempDate) as Giorno,
       Week(TempDate) AS Settimana,  
       WeekDay(TempDate) AS GiornoSettimana,
       Year(TempDate) * 1000 + Week(TempDate) as AnnoSettimana
RESIDENT TempCalendar ORDER BY DateNumber ASC;
drop Table TempCalendar;

end sub

and finally the "Fornitori" table is created like this:

Fornitori:
LOAD Distinct 
Fornitori.URL	as [% Id Fornitore],
Fornitori.NAME 	as [Nome Fornitore],  
Fornitori.RagioneSociale as [Ragione sociale Fornitore], 
Date(Date#([Fornitori.Data],'dd/MM/yyyy hh:mm:ss'),'dd/MM/yyyy')    as [Data Fornitore], 
Fornitori.PIva 	 as [PIVA Fornitore]
FROM
qvd\Fornitori.qvd
(qvd);

I made only two selections:
1) From the  List Box Object "Fornitori" I have selected a supplier

2)From the two Calendar Object "Da: and A:" I have selected the dates of interest

thanks

[sunny_talwar]

Did you forget to attach the qvw? 🙂

[Max1984]

The qvd files that are contained in a "qvd" folder.

[Max1984]

Attached the main file.

thanks

[sunny_talwar · Accepted solution]

Does this look right?

[Max1984]

Quindi per ottenere il tavolo in questo modo ora:

[sunny_talwar]

They are the same, are they not?

[Max1984]

Yes, but I want to hide the total of the "DARE" and "AVERE" columns and display only the "SALDO" column by changing the "Totale"  label with "Saldo Iniziale"