Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Max1984
Contributor III
Contributor III

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

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:

mastrrino.jpg

the pivot table I want to realize is this:
 
Immagine (1).png
 thanks.
Labels (4)
7 Replies
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...

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Max1984
Contributor III
Contributor III
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Max1984
Contributor III
Contributor III
Author

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

these are the qvd used

 

thanks

Max1984
Contributor III
Contributor III
Author

 

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

))
 
ImageR.jpg
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
Contributor III
Contributor III
Author

 

Island:

Load * Inline [

Dim

1

2

3

4

5

];

 

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:

Immagine Ok.jpg

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