Totals on Pivot table are incorrect

[cseward1963]

The pivot table has 1 row,  2 columns and 1 measure.  The row is School Building, the 2 columns are School Year and Therapy Type.  The measure function looks like this:

count(distinct if ( (consult + individual + direct)>0,studentid))

the function counts the studentids who received Consult, Individual or Direct services

The totals by School Building are correct, but the grand totals are not.

Any help is surely appreciated.

Celia

[cseward1963]

Sunny, are you saying that his suggestion makes sense?  I don't believe I have access to that Chart Properties window. 

[sunny_talwar]

No, I was pointing out that this thread is related to Qlik Sense

[cseward1963]

it seems that no matter what expression I put in the Expression (fx), nothing changes on the totals; it's as if there is no effect.  Maybe this is not supported in Qlik Sense Cloud??

I'm so stuck on this.   

[rwunderlich]

This is probably not your problem, but as a best practice avoid summing fields with the "+" operator;

consult + individual + direct

If any of those fields are null, the entire result will be null. A safer alternative is

RangeSum(consult, individual, direct)

RangeSum will treat nulls as zero so will always return a numeric result.

Can you post a screenshot of your pivot table?  (Masking out the school name or other sensitive info)

-Rob

[cseward1963]

Thanks for the reply Bob.  I'm having trouble pasting the screenshot.   The pivot table has 3 dimensions; school is a row, discipline is column 1 and school year is column 2 underneath discipline.  The measure is a function as follow which I changed to use rangesum as you suggested, the function looks like this:

count(distinct if ( RangeSum(direct,individual,consult)>0,studentid))

the student count by school are correct.  The total of all the school rows is not.  below is kind of what the pivot table looks like, the yellow totals are Wrong, but the individual school totals are correct.

SCHOOL	Occupational Therapy	Physical Therapy	Speech Therapy
	2016-2017	2016-2017	2016-2017
Total	168	38	26
school	32	3	-
school	16	4	-
school	-	8	-
school	14	0	-
school	1	0	-
school	19	6	26
school	-	-	-
school	32	7	0
school	0	0	2
school	47	7	-
school	0	4	0
school	21	5	2

[rwunderlich]

As your totals are less than the sum of rows, my speculation is the the same studentid appears in different schools? If so, I can suggest a couple of options.

1. If the duplicate studentid really represents different students -- e.g. School A and School B both have a studentid=1, but they represent different people, then modify your expression to include the School like this:

count(distinct if ( RangeSum(direct,individual,consult)>0, School & studentid))

2. If studentid is unique across the schools, e.g. studentid=1 (same person) was served at two different schools, and you want to count them twice, then you have the "Pivot Table Sum of Rows" issue. See this article on how to resolve:

Sum of rows in pivot tables ‒ QlikView ‌The article is in the QlikView Help, but applies equally well to Qlik Sense.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[cseward1963]

Hi Rob.  My reply is way overdue (sorry), but i had to stop my qlik sense project and do other things for the start of the new school year.  Your answer above makes total sense now and I should have realized it myself.  The caseload report that I wrote with TSQL/SSRS deals with students appearing in more than 1 school by looping through and choosing the school  where the student was most recently treated so the student is only counted once.  I'll have to figure a way to do this in qlik - I wish I could invoke some kind of looping routine each time a caseload date range is selected. Don't know what I'm going to do yet, BUT thanks for explaining why the totals were not correct!  it was driving me crazy.

Celia