Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
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))))``````

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

Labels (4)

• ### Set Analysis

2 Solutions

Accepted Solutions
MVP

Does this look right?

MVP

Check attached

11 Replies
MVP

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

Contributor III
Author

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:
[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],
FROM
qvd\fatture.qvd
(qvd);
Concatenate(Fatture_Passive)
Num(Floor(Date#([ElencoReportScadenzeA.DataScadenza],'dd/MM/yyyy hh:mm:ss')))             as  [% Master Calendar Date Num],
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);``````

The calendar is created by the following script:

``````sub MasterCalendar

tmp_minmaxdate:
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:
\$(vDateMin) + RowNo() - 1 AS DateNumber,
\$(vDateMin) + RowNo() - 1 AS TempDate
AUTOGENERATE 1
WHILE \$(vDateMin) +IterNo()-1 <= \$(vDateMax);

Calendar:
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:
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

MVP

Did you forget to attach the qvw? 🙂

Contributor III
Author

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

Contributor III
Author

Attached the main file.

thanks

MVP

Does this look right?

Contributor III
Author

Quindi per ottenere il tavolo in questo modo ora:

MVP

They are the same, are they not?

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"

Community Browser