topic Re: problem with total label in pivot table in QlikView

problem with total label in pivot table

Nagaraju_KCS — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I have a pivot table, which shows partial sums for the third dimension i.e Month-Year.

Total label showing twice.

Thanks in Advance...

Re: problem with total label in pivot table

Sergey_Shuklin — Fri, 21 Jul 2017 11:26:09 GMT

Hello, Raju!

Name the first total as "total" and the second total as ' '.

Re: problem with total label in pivot table

Nagaraju_KCS — Fri, 21 Jul 2017 11:32:36 GMT

Sorry, i didn't get you.

Re: problem with total label in pivot table

neha_shirsath — Fri, 21 Jul 2017 11:35:59 GMT

This is coming 2 times because it consider Month year and sub product row wise so for both it shows differently Total field.

Re: problem with total label in pivot table

Nagaraju_KCS — Fri, 21 Jul 2017 11:42:20 GMT

Yes, i understand that.

but, how to get rid of that.

Re: problem with total label in pivot table

neha_shirsath — Fri, 21 Jul 2017 11:46:24 GMT

I think we will not removed 1 because removing of one total text, its removing both so better way you need to do some extra thing like you can take text object and keep on 1 Total to hide that.

Re: problem with total label in pivot table

Nagaraju_KCS — Fri, 21 Jul 2017 11:52:44 GMT

if users select any value, it will reduce chart, it will appear again.

That is not correct way.

Re: problem with total label in pivot table

Fri, 21 Jul 2017 11:59:09 GMT

Hi,

The issue is due to subtotal enabled for Month-Year and the dimension is placed below the expression name.

Screen shot for this explanation is given below.

When Month-Year is shown horizontally and below expression name.

When Month-Year is given above the expression names.

In the bottom screen shot you see only 1 total.

In the above screen shot it shows 2 total, top Total is for expressions and bottom Total is for Month-Year.

Hope it helps

Re: problem with total label in pivot table

Nagaraju_KCS — Fri, 21 Jul 2017 12:21:09 GMT

I know that, problem is, if i drag up my dimension, users always scroll to see totals.

they need to see each level sub totals.

Re: problem with total label in pivot table

Nagaraju_KCS — Fri, 21 Jul 2017 12:21:46 GMT

Experts, anyone can help me.

Re: problem with total label in pivot table

Sergey_Shuklin — Fri, 21 Jul 2017 18:08:12 GMT

Hello, Raju!

Here you go!

Re: problem with total label in pivot table

bharat19s — Fri, 29 Dec 2017 12:21:58 GMT

Hi,

I am facing the same issue, Total comes twice as Label. & I am using Personal Edition, unable to open your file "Pivot.qvw".

Can you suggest the logic/ procedure for the same?

Re: problem with total label in pivot table

sunny_talwar — Fri, 29 Dec 2017 13:08:50 GMT

Here is what serj_shu‌ did

LOAD * INLINE [

Category, Product, SubProduct, Revenue, Contribution, Month-Year

A, A1, A11, 100, 362, Jan-2017

A, A2, A22, 120, 222, Jan-2017

A, A3, A33, 135, 55, Feb-2017

A, A4, A44, 452, 66, March-2017

B, B1, B11, 685, 544, Jan-2017

B, B2, B22, 752, 8888, Feb-2017

B, B3, B33, 698, 999, March-2017

C, C1, C11, 4545, 555, Jan-2017

C, C2, C22, 45, 556, March-2017

D, D1, D11, 4545, 125, Jan-2017

D, D2, D22, 988, 362, Feb-2017

D, D3, D33, 555, 251, March-2017

];

Join (t)

LOAD Category, Product, SubProduct, 'Total' as [Month-Year], sum(Revenue) as Revenue, sum(Contribution) as Contribution Resident t

Group by Category, Product, SubProduct;

he essentially pre-aggregate the total in the script.... but this may slow down your script...

Here is an alternative

LOAD * INLINE [

Category, Product, SubProduct, Revenue, Contribution, Month-Year

A, A1, A11, 100, 362, Jan-2017

A, A2, A22, 120, 222, Jan-2017

A, A3, A33, 135, 55, Feb-2017

A, A4, A44, 452, 66, March-2017

B, B1, B11, 685, 544, Jan-2017

B, B2, B22, 752, 8888, Feb-2017

B, B3, B33, 698, 999, March-2017

C, C1, C11, 4545, 555, Jan-2017

C, C2, C22, 45, 556, March-2017

D, D1, D11, 4545, 125, Jan-2017

D, D2, D22, 988, 362, Feb-2017

D, D3, D33, 555, 251, March-2017

];

Dim:

LOAD * Inline [

Dim

];

Where Dim is going to be island table and then do this

Dimensions

Category

Product

SubProduct

=Pick(Dim, [Month-Year], 'Total')

Expressions

Sum(Revenue)

Sum(Contribution)

Background Color expression for both your expressions and the last calculated dimension

=If(Dim = 2, RGB(192,192,192))

Text format for both your expressions and the last calculated dimension

=If(Dim = 2, '<b>')

Re: problem with total label in pivot table

bharat19s — Wed, 03 Jan 2018 06:29:21 GMT

Hi @Sunny

In my case, I am using Total column with Aggr function.

Can you help, how to handle such conditions.

Re: problem with total label in pivot table

sunny_talwar — Wed, 03 Jan 2018 09:31:40 GMT

With Aggr? Can you elaborate a little on this?

Re: problem with total label in pivot table

bharat19s — Wed, 03 Jan 2018 10:25:52 GMT

Hi,

My report is related to Revenue per employee, (Hotel Data & HR Data).

Last Month & Last Year - Last month are my expression, while Zone, Cluster and Hotel are dimensions, and Dept is my calculated dimension.

So, In Expression I am using , Sum(revenue) / Count(Emp).

But for total column it sholud be the SUM of all department revenue per employee value.

To get such value as total, I am using Aggr function.

Re: problem with total label in pivot table

sunny_talwar — Wed, 03 Jan 2018 13:03:33 GMT

Again, I don't see (or understand) the problem, would you be able to share a qvw sample to show your issue?

Re: problem with total label in pivot table

bharat19s — Wed, 03 Jan 2018 14:01:02 GMT

Total Sub-Column of Last Month should give :: Sum of (Food + SPA + HR +HA) values i.e. Revenue per Employee.

But according to our logic it is giving over all revenue divide by all number of employees.

I want actual value marked by red circle in below image, but i am getting by formula value:

Re: problem with total label in pivot table

sunny_talwar — Wed, 03 Jan 2018 14:11:42 GMT

Would you be able to share the above data in an Excel format?

Re: problem with total label in pivot table

bharat19s — Thu, 04 Jan 2018 06:51:09 GMT

In Expression, I am using SUM(Revenue) / Count(EMP_ID)