Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amiumi66
New Contributor III

Pivot Table Total based on different column

SchoolSubjectStudentsGradesABCMathab0ABCMathac12ABCMathad32ABDMathae1BBEnglishuu44BBEnglishud0BBEnglishua

33

SchoolSubjectStudentsGrades

SchoolSubjectStudentsGradesClass Total

Variance

ABCMathab105510/55
ABCMathac125512/55
ABCMathad325532/55
ABDMathae1551/55
BBEnglishuu448744/87
BBEnglishud108710/87
BBEnglishua

33

8733/87

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot Table Total based on different column

What is wrong here my friend?

Capture.PNG

14 Replies
MVP
MVP

Re: Pivot Table Total based on different column

Should be like this

Class Total

Sum(TOTAL <School> Grades)

Variance

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

vishsaggi
Esteemed Contributor III

Re: Pivot Table Total based on different column

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.

MVP
MVP

Re: Pivot Table Total based on different column

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)

amiumi66
New Contributor III

Re: Pivot Table Total based on different column

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.

amiumi66
New Contributor III

Re: Pivot Table Total based on different column

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.

amiumi66
New Contributor III

Re: Pivot Table Total based on different column

SchoolSubjectStudentsGrades   Variance

ABCMathab10  10/55
ABCMathac1212/55
ABCMathad3232/55
ABCMathae11/55
BBEnglishuu4444/87
BBEnglishud1010/87
BBEnglishua

33

33/87
vishsaggi
Esteemed Contributor III

Re: Pivot Table Total based on different column

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)

amiumi66
New Contributor III

Re: Pivot Table Total based on different column

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.

MVP
MVP

Re: Pivot Table Total based on different column

What is wrong here my friend?

Capture.PNG