Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

lawrenceiow
Contributor II

Sum of Columns in Pivot Table

I am struggling with summing up columns (or is it rows?) in a pivot table.

I need a Sum for the row headed Area1, Area2, etc

I created a pivot table with the Dimensions Location and DateReported and the Expression Num(Max(F2)+Max(F3)), this is because I need to know the maximum value of F2 and F3 per day

In the pivot table I have Location in column1 and DateReported represents column headings...

LocationDateReported

01-Jan-18

02-Jan-1803-Jan-18
Area1161716
Area2373737
Area3141213
Area4282829

How do I get a sum for each Location? If I use Show Partial Sums against DateReported I just get Max, eg:

Area1 is 17, Area2 is 37, Area3 is 14 and Area4 is 29. It should be...

Area1 is 49, Area2 is 111, Area3 is 39 and Area4 is 85

The dataset is built up by loading many copies of a "snapshot" report, i.e. the Excel file is saved with a date/Time in the filename at various points during the day.

The facts I'm reading from each file are Location, Filename, DateTimeReported, DateReported, MonthOfDateReported F2 and F3

1 Solution

Accepted Solutions

Re: Sum of Columns in Pivot Table

Use this expression

Num(Sum(Aggr(Max(F2)+Max(F3), Location, DateReported)))

and enable 'Show Partial Sums'

View solution in original post

6 Replies

Re: Sum of Columns in Pivot Table

Try this may be

Num(Sum(Aggr(Max(F2)+Max(F3), Location, DateReported)))

Also, look here

Sum of rows in pivot tables ‒ QlikView

rogarol24
New Contributor II

Re: Sum of Columns in Pivot Table

Hi Lawrence,

You can try using this formula as expression:

=COLUMN(1) + COLUMN(2) + COLUMN(3)

Hope this helps,

Rodrigo.

lawrenceiow
Contributor II

Re: Sum of Columns in Pivot Table

I had already tried the method in that link but by adding another expression I get two numbers under each date (I labelled the expression as Tot).

DateReported

01-Jan-18

02-Jan-1803-Jan-18
LocationMaxTotMaxTotMaxTot
Area1161617171616
Area2373737373737
Area3141412121313
Area4282828282929

Re: Sum of Columns in Pivot Table

Use this expression

Num(Sum(Aggr(Max(F2)+Max(F3), Location, DateReported)))

and enable 'Show Partial Sums'

View solution in original post

lawrenceiow
Contributor II

Re: Sum of Columns in Pivot Table

Excellent, now we're getting somewhere. I have a total at the end of the row.

But, how do I suppress the Tot showing under each date?

Actually, forget that, I removed my original expression (  =Num(Max(F1)+Max(F2))  ) and that worked.

Thank you stalwar1‌, and very fast replies too, thank you very much

Re: Sum of Columns in Pivot Table

Don't use two expression... just use one expression... the new one that I just gave you