QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Contributor

How can we calculate the Sum of Rows in a Pivot Table ?

Hi All,

I have an expression that goes like this

if(Allow = 'Y', SUM({\$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),

if(Allow <> 'Y' and Contract = '1',

SUM({\$< DirAmount={'-1'}, Enddate = {'>=\$(vEndDate)'}>} POAmount + ConAmount), 0))

I am getting the correct values when displaying this in a straight table with checking the Sum of rows checkbox or when exporting to excel.

How can I display the sum of row values in a pivot table ? Or even display it in a straight table by checking the Expressions Total option.

Thanks,

Umar

1 Solution

Accepted Solutions
MVP

Re: How can we calculate the Sum of Rows in a Pivot Table ?

You can do this using advanced aggregation (aggr() function). There is a chapter in the Help on how to use aggr() function to emulate sum-of-rows in a pivot table.

Basically it looks like:

= sum(

aggr(

if(Allow = 'Y', SUM({\$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),

if(Allow <> 'Y' and Contract = '1',

SUM({\$< DirAmount={'-1'}, Enddate = {'>=\$(vEndDate)'}>} POAmount + ConAmount), 0))

,YourChartDim1, YourChartDim2)

)

You need to replace YourChartDim1 etc. with all your chart dimensions (fields only!).

2 Replies
MVP

Re: How can we calculate the Sum of Rows in a Pivot Table ?

You can do this using advanced aggregation (aggr() function). There is a chapter in the Help on how to use aggr() function to emulate sum-of-rows in a pivot table.

Basically it looks like:

= sum(

aggr(

if(Allow = 'Y', SUM({\$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),

if(Allow <> 'Y' and Contract = '1',

SUM({\$< DirAmount={'-1'}, Enddate = {'>=\$(vEndDate)'}>} POAmount + ConAmount), 0))

,YourChartDim1, YourChartDim2)

)

You need to replace YourChartDim1 etc. with all your chart dimensions (fields only!).

Contributor

Re: How can we calculate the Sum of Rows in a Pivot Table ?

Great ! Thank you Sir.

Regards,

Umar