Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cseward1963
Creator
Creator

Totals on Pivot table are incorrect

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

16 Replies
sunny_talwar

May be use Sum(Aggr())...

Sum(Aggr(Count(DISTINCT If((consult + individual + direct)>0, studentid)), Dimensions))

or this

Sum(Aggr(Count(DISTINCT {<studentid = {"=consult + individual + direct > 0"}>} studentid), Dimensions))

devarasu07
Master II
Master II

Hi,

Like this?

=count({$<consult={">0"},individual={">0"},direct={">0"}>} distinct studentid)

or

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

cseward1963
Creator
Creator
Author

I will try you suggestions, but where do I enter this function to replace to "built-in" totals (the ones that are wrong on my pivot table)? 

devarasu07
Master II
Master II

Hi,

In your pivot chart --expression total mode change to "Sum of rows"

like this

Capture.JPG

sunny_talwar

I think this is Sense Devarasu

sunny_talwar

Just replace your existing expression with the new one

devarasu07
Master II
Master II

Hi,

Refer to the sense - in build total mode. and here you can write your custom total expression. still if you have issue then share with us your mock app. tks

Capture.JPG

cseward1963
Creator
Creator
Author

I use Qlik Sense Cloud.  How do I get to the Chart Properties Window in Davarasu's reply above? 

cseward1963
Creator
Creator
Author

I should say that I don't have access to an "Expressions" tab.