topic Re: Pivot table cumulative sum initial and current balance in QlikView

Pivot table cumulative sum initial and current balance

Max1984 — Sat, 16 Nov 2024 01:44:18 GMT

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

Re: Pivot table cumulative sum initial and current balance

sunny_talwar — Tue, 10 Dec 2019 12:35:02 GMT

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

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 13:18:43 GMT

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

Re: Pivot table cumulative sum initial and current balance

sunny_talwar — Tue, 10 Dec 2019 13:32:19 GMT

Did you forget to attach the qvw? 🙂

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 14:24:50 GMT

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

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 14:30:32 GMT

Attached the main file.

thanks

Re: Pivot table cumulative sum initial and current balance

sunny_talwar — Tue, 10 Dec 2019 14:55:48 GMT

Does this look right?

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 15:08:27 GMT

Quindi per ottenere il tavolo in questo modo ora:

Re: Pivot table cumulative sum initial and current balance

sunny_talwar — Tue, 10 Dec 2019 15:12:34 GMT

They are the same, are they not?

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 15:43:35 GMT

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"

Re: Pivot table cumulative sum initial and current balance

sunny_talwar — Tue, 10 Dec 2019 15:46:41 GMT

Check attached

Re: Pivot table cumulative sum initial and current balance

Max1984 — Tue, 10 Dec 2019 15:55:20 GMT

Very well,

thanks