topic Re: How to Sum Column(with condition) in Pivot Table in QlikView

How to Sum Column(with condition) in Pivot Table

Anonymous — Thu, 06 Jun 2013 02:18:19 GMT

Hi,

I've tried to search around this forum but I can't find a solution (or may be I need more detailed guidance) in order to solve my problem.

Refer to the table below, I would like to get the data in red text.

Name	Class	Math Score	Science Score
StudentA	B	20	40
StudentB	D	10	30
StudentC	B	30	20
StudentD	D	20	10
Total Score for Class B		50	60
Total Score for Class D		30	40

I've also attached a sample QV application. Hope someone experienced can show me the changes in the application and re-attach the application.

Thanks in advance and have a nice day.

Re: How to Sum Column(with condition) in Pivot Table

calvindk — Thu, 06 Jun 2013 02:35:30 GMT

Try "show partial sums" on the presentation tab, and change dimension to have class first, student after.

That would look like this:

Not sure if that is good enough?

Re: How to Sum Column(with condition) in Pivot Table

Anonymous — Thu, 06 Jun 2013 02:45:17 GMT

Hi Anders Møller,

I've tried your steps, to show partial sums for 'Class' on the presentation tab, then moved the 'Class' to become the first dimension, but I can't get the same result. Did i left out anything?

Re: How to Sum Column(with condition) in Pivot Table

Sokkorn — Thu, 06 Jun 2013 02:49:39 GMT

Hi,

Let do this

1. Change expression

[Math Score] to Sum([Math Score])

[Science Score] to Sum([Science Score])

2. Drag [Class] field to first column and [Name] should be second column. Table header like this

Class Name Sum([Math Score]) Sum([Science Score])

3. Goto Presentation -> Select "Name" dimension -> Check Show Partial Sums

See sample attached file

Regards,

Sokkorn

Re: How to Sum Column(with condition) in Pivot Table

Anonymous — Thu, 06 Jun 2013 02:54:01 GMT

Hi Sokkorn Cheav (Cambodia),

Thanks for the prompt reply. It's a good idea. But your solution will give me something like this:

Class	Name	Math Score	Science Score
B	StudentA	20	40
	StudentC	30	20
	Total Score for Class B	50	60
D	StudentB	10	30
	StudentD	20	10
	Total Score for Class D	30	40

But for my case, I need the data to be all sorted by 'Student Name', while the solution that you suggest me is firstly sort by 'Class', then sort of 'Student Name'. I would need something exactly like below (if possible):

Name	Class	Math Score	Science Score
StudentA	B	20	40
StudentB	D	10	30
StudentC	B	30	20
StudentD	D	20	10
Total Score for Class B		50	60
Total Score for Class D		30	40

Hope to hear from someone who can help me. Thanks in advance!

Re: How to Sum Column(with condition) in Pivot Table

Anonymous — Thu, 06 Jun 2013 06:08:26 GMT

Hi All,

Hope someone can give me some idea on this question. Your help is greatly appreciated.

Thanks in advance.

Re: Re: How to Sum Column(with condition) in Pivot Table

Tue, 08 Oct 2013 08:17:52 GMT

Why not create a new expression and add your set analysis. A bit messy but effective.