Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

2 Solutions

Accepted Solutions
sunny_talwar

Does this look right?

image.png

View solution in original post

sunny_talwar

11 Replies
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
Contributor III
Contributor III
Author

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

image1.jpg

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
Contributor III
Contributor III
Author

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

 

Max1984
Contributor III
Contributor III
Author

Attached the main file.

thanks

sunny_talwar

Does this look right?

image.png

Max1984
Contributor III
Contributor III
Author

Final.jpgQuindi per ottenere il tavolo in questo modo ora:

sunny_talwar

They are the same, are they not?

image.png

Max1984
Contributor III
Contributor III
Author

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"