Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

totalling in pivot table

Hi Experts,

i have a pivot table calculation on counting invoices by month

and the expression is

Expr : Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

my table looks like before.jpeg

i want the sum off all rows as total. so i have used like

Expr :

=sum(AGGR(

Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

,BRCODE

,BRNAME

,RSMNAME

))

then i m not getting values for all branch in all months.

the table looks like after.jpeg.

how can i sort out the issue.

please help

1 Solution

Accepted Solutions
Highlighted

Re: totalling in pivot table

Try this:

=sum(AGGR(

Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

,BRCODE

,BRNAME

,RSMNAME,

CalendarMonth //YourPivoted Dimension

))

If this doesn't work, then try this:

=sum(AGGR(NoDistinct

Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

,BRCODE

,BRNAME

,RSMNAME,

CalendarMonth //YourPivoted Dimension

))

Best,

Sunny

View solution in original post

2 Replies
Highlighted

Re: totalling in pivot table

Try this:

=sum(AGGR(

Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

,BRCODE

,BRNAME

,RSMNAME,

CalendarMonth //YourPivoted Dimension

))

If this doesn't work, then try this:

=sum(AGGR(NoDistinct

Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)

,BRCODE

,BRNAME

,RSMNAME,

CalendarMonth //YourPivoted Dimension

))

Best,

Sunny

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: totalling in pivot table

Add your CalendarMon.... dimension to the aggr too.


talk is cheap, supply exceeds demand