Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Pivot Chart-Total calc Issue

Hi,

Raw data looks like this

NBRTimestatus
10010/15/2011 11.30PMdone
10010/15/2011 11.35PMdone
10110/15/2011 11.36PMdone
10210/15/2011 11.37PMdone
10310/15/2011 11.38PMdone


Now in the pivot table

NBRCtFct
10020
10111
10211
10311
TOTAL50


Here Ct and Fct are the Expressions.

For Calculation of Ct,I have used

Count(DISTINCT TOTAL<nbr>timeEnter)

and for the Fct,I have used if([Ct]=1,1,0)

When I do a partial sum on Nbr,I am getting Total for Fct as 0 (where as expected val is 3).

Can someone help me on this?

Thanks,Pooja

1 Solution

Accepted Solutions
MVP
MVP

Pivot Chart-Total calc Issue

Pooja,

since you don't use Time or timeEnter as dimension, you don't need to use the TOTAL<nbr> here, just

=count(DISTINCT timeEnter)

should be enough.

For the Fct column, the TOTAL is evaluated as expression total in a pivot table, so the TOTAL expression is equivalent to if(5=1,1,0), resulting in 0, it is not a sum of rows expression.

You could either use a straight table chart with sum of rows as Total mode in expression tab, or stick with the pivot and use advanced aggregation to get your sum of rows in the TOTAL line (check out the Help, searching for sum of rows in pivot table using advanced aggregation):

=sum(aggr(if(count(DISTINCT Time)=1,1,0),NBR))

Hope this helps,

Stefan

3 Replies
MVP
MVP

Pivot Chart-Total calc Issue

Pooja,

since you don't use Time or timeEnter as dimension, you don't need to use the TOTAL<nbr> here, just

=count(DISTINCT timeEnter)

should be enough.

For the Fct column, the TOTAL is evaluated as expression total in a pivot table, so the TOTAL expression is equivalent to if(5=1,1,0), resulting in 0, it is not a sum of rows expression.

You could either use a straight table chart with sum of rows as Total mode in expression tab, or stick with the pivot and use advanced aggregation to get your sum of rows in the TOTAL line (check out the Help, searching for sum of rows in pivot table using advanced aggregation):

=sum(aggr(if(count(DISTINCT Time)=1,1,0),NBR))

Hope this helps,

Stefan

MVP & Luminary
MVP & Luminary

Re: Pivot Chart-Total calc Issue

Hi Pooja,

What Swuehl said is absolutely correct, since there is a single dimension using straight table is better option, by using the "Sum of Rows" option the Total would be corrected or else we need to use Sum and AGGR function in Pivot table.

Please check the attached file, it is similar to what Swuehl has suggested.

Regards,

Jagan.

Not applicable

Pivot Chart-Total calc Issue

Thanks to both of you for your help on this and for the explanation.

Community Browser