Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cseward1963
Contributor

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
MVP
MVP

Re: Totals on Pivot table are incorrect

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
Honored Contributor II

Re: Totals on Pivot table are incorrect

Hi,

Like this?

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

or

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

cseward1963
Contributor

Re: Totals on Pivot table are incorrect

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
Honored Contributor II

Re: Totals on Pivot table are incorrect

Hi,

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

like this

Capture.JPG

MVP
MVP

Re: Totals on Pivot table are incorrect

I think this is Sense Devarasu

MVP
MVP

Re: Totals on Pivot table are incorrect

Just replace your existing expression with the new one

devarasu07
Honored Contributor II

Re: Totals on Pivot table are incorrect

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
Contributor

Re: Totals on Pivot table are incorrect

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

cseward1963
Contributor

Re: Totals on Pivot table are incorrect

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