topic Re: Pivot Table Total based on different column in QlikView

Pivot Table Total based on different column

userid128223 — Mon, 13 Mar 2017 15:35:35 GMT

SchoolSubjectStudentsGradesABCMathab0ABCMathac12ABCMathad32ABDMathae1BBEnglishuu44BBEnglishud0BBEnglishua

School

Subject

Students

Grades

School	Subject	Students	Grades	Class Total	Variance
ABC	Math	ab	10	55	10/55
ABC	Math	ac	12	55	12/55
ABC	Math	ad	32	55	32/55
ABD	Math	ae	1	55	1/55
BB	English	uu	44	87	44/87
BB	English	ud	10	87	10/87
BB	English	ua	33	87	33/87

How can i calculate Class Total. will it be formulat or set analysis.

Re: Pivot Table Total based on different column

sunny_talwar — Mon, 13 Mar 2017 15:38:25 GMT

Should be like this

Class Total

Sum(TOTAL <School> Grades)

Variance

Sum(Grades)/Sum(TOTAL <School> Grades)

Re: Pivot Table Total based on different column

vishsaggi — Mon, 13 Mar 2017 15:49:25 GMT

I think mm has to clarify if it is typo or expecting to be aggr based on School. Coz his school name has ABD (will it be ABC or ABC only) as his total is 55.

Re: Pivot Table Total based on different column

sunny_talwar — Mon, 13 Mar 2017 15:52:03 GMT

That seems like a typo... but it might be aggr on School and Subject I think

Class Total

Sum(TOTAL <School, Subject> Grades)

Variance

Sum(Grades)/Sum(TOTAL <School, Subject> Grades)

Re: Pivot Table Total based on different column

userid128223 — Tue, 21 Mar 2017 18:52:01 GMT

Yes that was typo.

and i am not getting expected results. i believe we may have to use aggr function to get total by school count.

Re: Pivot Table Total based on different column

userid128223 — Tue, 21 Mar 2017 18:53:30 GMT

in my case i forgot to mention. Class Total field don't exist. I have to determine class total by School name and based on that used the total.

Re: Pivot Table Total based on different column

userid128223 — Tue, 21 Mar 2017 18:56:10 GMT

School	Subject	Students	Grades	Variance
ABC	Math	ab	10	10/55
ABC	Math	ac	12	12/55
ABC	Math	ad	32	32/55
ABC	Math	ae	1	1/55
BB	English	uu	44	44/87
BB	English	ud	10	10/87
BB	English	ua	33	33/87

Re: Pivot Table Total based on different column

vishsaggi — Tue, 21 Mar 2017 19:06:10 GMT

What is the problem you are facing? Did you try Sunny's below expressions?

Dim:

School

SUbject

Students

Grades

Exprs:

ClassTotals

Sum(TOTAL <School> Grades)

Variance

Sum(Grades)/Sum(TOTAL <School> Grades)

Re: Pivot Table Total based on different column

userid128223 — Tue, 21 Mar 2017 19:14:44 GMT

sorry i did not have total field. I was just trying to explain where it was comming from. Your expression don't give me right results. I think we will have to use Aggr function to get this results.

Re: Pivot Table Total based on different column

sunny_talwar — Tue, 21 Mar 2017 19:18:16 GMT

What is wrong here my friend?

Re: Pivot Table Total based on different column

sunny_talwar — Tue, 21 Mar 2017 19:19:56 GMT

In case Aggr() makes you happy (which doesn't make me happy because it effects performance), you can use this

Sum(Grades)/Aggr(NODISTINCT Sum(Grades), School)

Re: Pivot Table Total based on different column

userid128223 — Wed, 22 Mar 2017 16:02:01 GMT

Hi Sunnny

Would the formula work the same way if aggrigation was "Count" and not "SUM"

Re: Pivot Table Total based on different column

sunny_talwar — Wed, 22 Mar 2017 16:02:41 GMT

I believe it would...

Re: Pivot Table Total based on different column

userid128223 — Wed, 22 Mar 2017 16:02:50 GMT

Class Total

count(TOTAL <School, Subject> Grades) ?

Variance

count (Grades)/Sum(TOTAL <School, Subject> Grades) ?

or do we have to rewrite it differently.

Re: Pivot Table Total based on different column

sunny_talwar — Wed, 22 Mar 2017 16:06:15 GMT