Sum of Columns in Pivot Table

[lawrenceiow]

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

Location	DateReported	01-Jan-18	02-Jan-18	03-Jan-18
Area1		16	17	16
Area2		37	37	37
Area3		14	12	13
Area4		28	28	29

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

[sunny_talwar]

Try this may be

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

Also, look here

Sum of rows in pivot tables ‒ QlikView

[Anonymous]

Hi Lawrence,

You can try using this formula as expression:

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

Hope this helps,

Rodrigo.

[lawrenceiow]

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-18		03-Jan-18
Location	Max	Tot	Max	Tot	Max	Tot
Area1	16	16	17	17	16	16
Area2	37	37	37	37	37	37
Area3	14	14	12	12	13	13
Area4	28	28	28	28	29	29

[sunny_talwar · Accepted solution]

Use this expression

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

and enable 'Show Partial Sums'

[lawrenceiow]

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

[sunny_talwar]

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

[AnkitaC]

Hi,

if i want to add total in pivot table like if 5 rows are there then sum of al 5 rows and after that need to remove first row value need to add only sum of last 4 rows how i will add in qlik sense pivot table