pivot table: accumulation starting from the balance preceding the selection date

[Max1984]

Good morning,

I wanted to ask two things:

1) How can I get the balance before the selection date?

for the current balance this is the formula used:

(
   RangeSum (Above (TOTAL SUM ({<[Master Calendar Date Calc] = {"> = $ (= $ Start (PC)) <= $ (= $ (vFinePC))"}, Year =, Month =, Day => } [# VAT Invoice Amount]), 0, RowNo (TOTAL)))
  
   -
  
   RangeSum (Above (TOTAL SUM ({<[Master Calendar Date Calc] = {"> = $ (= $ Start (PC)) <= $ (= $ (vFinePC))"}, Year =, Month =, Day => } [# Amount Paid Payments Deadlines]), 0, RowNo (TOTAL)))
  
)

2) Is it possible to put a row in the pivot table of the previous balance and the accumulation calculation starts from the previous balance?

the current table is this:

the pivot table I want to realize is this:

 thanks.

[Anil_Babu_Samineni]

Perhaps this

In script, Create Island table like

Island:

Load * Inline [

Dim

1

2

3

];

Pattern of object

Dim1:
Pick(Dim, 'Prev Month', [Data Reg.])

Dim2:
Pick(Dim, 'Prev Month', [Num. Doc.])

Expression for DARE an example

Pick(Dim, RangeSum(Above(Sum([# VAT Invoice Amount]), 0, Rowno(TOTAL))),
(RangeSum (Above (TOTAL SUM ({<[Master Calendar Date Calc] = {"> = $ (= $ Start (PC)) <= $ (= $ (vFinePC))"}, Year =, Month =, Day => } [# VAT Invoice Amount]), 0, RowNo (TOTAL)))
-RangeSum (Above (TOTAL SUM ({<[Master Calendar Date Calc] = {"> = $ (= $ Start (PC)) <= $ (= $ (vFinePC))"}, Year =, Month =, Day => } [# Amount Paid Payments Deadlines]), 0, RowNo (TOTAL)))))

etc...

[Max1984]

good morning,

the dimensions load them with the following script (see code in bold):

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],
Num#([Fatture Passive Admin.InmportoFatturaIva]) as [# Importo Fattura Iva],
[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);

To add a line at the top of the pivot table, do I have to enter another expression or should I just change the one I used?

[Anil_Babu_Samineni]

"Previous balance - Month or Year" - You want as single row? Or how need the row level

As per my understand, You can define within single expression once island table handy

[Max1984]

Hi

I would like a single line of the previous balance how can I get it? I am attaching the script I am using

thanks

[Max1984]

these are the qvd used

thanks

[Max1984]

In the script, I created the Island table as

Island:

Load * Inline [

Dim

1

2

3

];

In the object I used 2 dimensions:

1) Data Reg.:  Pick(Dim, 'Prev Month',Date#( [Data Reg]))

2) Num. Doc.: Pick(Dim, 'Prev Month',[Numero Fattura Passiva])

As an expression I used "Dare":

Pick(Dim,RangeSum(Above(Sum([# Importo Fattura Iva]),0,RowNo(TOTAL)))
,(
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)))

))

If I use the formula you suggested, it doesn't work. The formula must take into account only the period prior to the selected one (see calendar object) so the debit must be € 86505.19 and not € 146628.55 (total current and previous year).

[Max1984]

Island:

Load * Inline [

Dim

1

2

3

4

5

];

Hi Anil_Babu_Samineni,

I would like a table that takes into account the balance preceding the one selected by the calendar object; consequently the accumulation must start from the previous balance and not from the selected one.

for example (see image), if the balance is 86505.19 the first line should be 86505.19 - 9261.87 = € 77243.32,
the second line 77243.32 + 9261.87 = 86505.19 €, the third line 86505.19 + 5577.30 = 92082.49 €  etc ...

How can I do ? using your suggestion the result is this:

In the script I entered:

Island:
Load * Inline [
Dim
1

2

3

4

5

];

the five dimensions included in the pivots table:

1)Pick(Dim, 'Prev Month', Date#([Data Reg]))
2)Pick(Dim, 'Prev Month',[Numero Fattura Passiva])
3)Pick(Dim, 'Prev Month',Date#([Data Fattura]))
4)Pick(Dim, 'Prev Month',[Mezzo pagamento Scadenze Pagamenti])
5)Pick(Dim, 'Prev Month',[Descrizione Scadenze Pagamenti])

the three expressions in the pivot table 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: 
Pick(Dim,RangeSum(Above(Sum([# Importo Fattura Iva]),0,RowNo(TOTAL)))
,(
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)))
))

thanks