topic Re: Total Of Rows of Pivot Table in QlikView

Total Of Rows of Pivot Table

Mon, 03 Nov 2014 06:36:57 GMT

Hello All,

I have pivot table. Dimension 1= Year Dimension 2= Brand.

Expression is count( distinct ItemID).

YEAR Brand1 Brand2 Brand3 TOTAL

2011 2 5 6 10

2012 1 6 9 13

Now the issue is that the Total in Pivot is taking the expression as count( Distinct ItemID).

How can i have total 2011 = 11, and 2012 = 16.

Thanks

Re: Total Of Rows of Pivot Table

Mon, 03 Nov 2014 07:14:41 GMT

Hi King,

Can you please try this one in your Expression tab.

Thanks and Regards

Sovan

Re: Total Of Rows of Pivot Table

its_anandrjs — Mon, 03 Nov 2014 07:47:03 GMT

Hi,

Yes you can use the Sum of Rows option in Chart properties >> Expression >> Total Mode.

Regards

Anand

Re: Total Of Rows of Pivot Table

Mon, 03 Nov 2014 07:56:49 GMT

This is for straight table not for pivot.

Re: Total Of Rows of Pivot Table

ashfaq_haseeb — Mon, 03 Nov 2014 08:03:32 GMT

Hi,

Post you sample data and expected o/p.

Regards

ASHFAQ

Re: Total Of Rows of Pivot Table

ashfaq_haseeb — Mon, 03 Nov 2014 08:04:36 GMT

You can't have such report with straight table.

Regards

ASHFAQ

Re: Total Of Rows of Pivot Table

simenkg — Mon, 03 Nov 2014 08:05:54 GMT

You have to use AGGR.

sum(Aggr(Count(Distinct ItemID),Year,BrandID))

Re: Total Of Rows of Pivot Table

jagan — Mon, 03 Nov 2014 08:05:56 GMT

Hi King,

Try this expression

=Sum(Aggr(count( Distinct ItemID), Brand))

Hope this helps you.

Regards,

Jagan.

Re: Total Of Rows of Pivot Table

Mon, 03 Nov 2014 08:27:09 GMT

Sample..

For Year 2012 it should be 4 not 3.

Re: Total Of Rows of Pivot Table

its_anandrjs — Mon, 03 Nov 2014 08:34:25 GMT

Hi,

In expression use Aggregation like

=Sum( Aggr( Count( DISTINCT ID),Brand,Year)) //because it has dimension Brand andYear

Note:- if you add more dimension in the chart then you have to use it in the Aggregation expression also

Regards

Anand

Re: Re: Total Of Rows of Pivot Table

simenkg — Mon, 03 Nov 2014 08:34:30 GMT

=sum(aggr(Count( DISTINCT ID),Year,Brand))

See attached file

Re: Total Of Rows of Pivot Table

jagan — Mon, 03 Nov 2014 08:44:00 GMT

Hi,

Try this expression

=Sum(Aggr(count( Distinct ID), Brand, Year))

Regards,

Jagan.

Re: Total Of Rows of Pivot Table

Mon, 03 Nov 2014 08:46:38 GMT

I have cyclic group with Year , month and Week.

Re: Total Of Rows of Pivot Table

jagan — Mon, 03 Nov 2014 08:48:47 GMT

Hi,

=Sum(Aggr(count( Distinct ID), Brand, $(=GetCurrentField(cyclicGroupName))))

Regards,

Jagan.

Re: Total Of Rows of Pivot Table

simenkg — Mon, 03 Nov 2014 08:49:05 GMT

sum(aggr(Count( DISTINCT ID),[$(=GetCurrentField(GroupName))],Brand))

Re: Total Of Rows of Pivot Table

jagan — Tue, 04 Nov 2014 01:07:41 GMT

Hi,

If you got the answer please close this thread by giving Correct Answer to the useful post.

Regards,

Jagan.