topic Sum of Columns in Pivot Table in QlikView

Sum of Columns in Pivot Table

lawrenceiow — Fri, 05 Jan 2018 18:16:25 GMT

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

Re: Sum of Columns in Pivot Table

sunny_talwar — Fri, 05 Jan 2018 18:25:06 GMT

Try this may be

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

Also, look here

Sum of rows in pivot tables ‒ QlikView

Re: Sum of Columns in Pivot Table

Anonymous — Fri, 05 Jan 2018 18:25:24 GMT

Hi Lawrence,

You can try using this formula as expression:

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

Hope this helps,

Rodrigo.

Re: Sum of Columns in Pivot Table

lawrenceiow — Fri, 05 Jan 2018 18:48:57 GMT

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

Re: Sum of Columns in Pivot Table

sunny_talwar — Fri, 05 Jan 2018 18:50:33 GMT

Use this expression

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

and enable 'Show Partial Sums'

Re: Sum of Columns in Pivot Table

lawrenceiow — Fri, 05 Jan 2018 18:53:37 GMT

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

sunny_talwar — Fri, 05 Jan 2018 18:55:09 GMT

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

Re: Sum of Columns in Pivot Table

AnkitaC — Tue, 01 Oct 2024 14:41:33 GMT

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