Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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:

Labels (4)

• ### qlik view

7 Replies

Perhaps this

In script, Create Island table like

Island:

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

good morning,

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

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],
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],

FROM
qvd\fatture.qvd
(qvd);

Concatenate(Fatture_Passive)
Num(Floor(Date#([ElencoReportScadenzeA.DataPagamento],'dd/MM/yyyy hh:mm:ss'))) as [Master Calendar Date Calc],
' '  as [Data Fattura],
ElencoReportScadenzeA.Fattura.URL as [% Id Fatture Passive],
ElencoReportScadenzeA.Fornitore.NAME as [Nome Fornitore Fatture Passive],
FROM
(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?

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

Contributor III
Author

these are the qvd used

thanks

Contributor III
Author

In the script, I created the Island table as

Island:

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

Contributor III
Author

``````Island:

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:

In the script I entered:

``````Island:
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])

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