topic Re: Pivot Table calculation in QlikView

Pivot Table calculation

yadav_anil782 — Tue, 06 Oct 2015 06:23:35 GMT

Hi Qlik Expets,

i have calculated column in pivot table and now i need one more column that will come against ( debit -credit )

suppose i have two column

Sale of Assets(Credit)

Sale of Assets(Debit)

then i need one column more, name will be Sale of Assets (Net)

logic will be Sale of Assets(Debit) - Sale of Assets(Credit)

i have attached same pivot table in excel plz find and try to solve my problem

Sale of Assets(Credit)- calculation is =Sum({<ACCOUNT_CODE = {"4332"}>}PERIOD_CR) and Label name is Sale of Assets(Credit)

Sale of Assets(Debit)- calculation is =Sum({<ACCOUNT_CODE = {"4332"}>}PERIOD_DR) and Label name is Sale of Assets(Debit)

Sale of Assets(Net) =??

Re: Pivot Table calculation

sujeetsingh — Tue, 06 Oct 2015 06:27:28 GMT

You can use Column 1- column2

Re: Pivot Table calculation

jonathandienst — Tue, 06 Oct 2015 06:34:19 GMT

You can either reuse the expressions:

=Sum({<ACCOUNT_CODE = {"4332"}>} PERIOD_DR) - Sum({<ACCOUNT_CODE = {"4332"}>} PERIOD_CR)

or you can reference the columns in the same table by name, or using the Column(n) function:

=[Sale of Assets(Debit)] - [Sale of Assets(Credit)]

Re: Pivot Table calculation

SreeniJD — Tue, 06 Oct 2015 06:35:23 GMT

As Sujeet mentioned,

Use these calculated columns in your expression and do a substraction from Debt to Credit.. Also handle null values and force them to "zero". this will works

Sreeni

Re: Pivot Table calculation

jonathandienst — Tue, 06 Oct 2015 06:35:53 GMT

Re: Pivot Table calculation

Anonymous — Tue, 06 Oct 2015 06:47:44 GMT

you can use if want to handle null values

if(isnull(ColumnNo(1)),0-ColumnNo(2),if(isnull(columnNo(2),ColumnNo(1)))