topic Re: Sub Totals ona pivot Table in QlikView

Sub Totals ona pivot Table

Wed, 25 Nov 2020 16:16:04 GMT

I need to add some sub totals for sales order quantity. sales order value etc. at customer code level.

I have ticked the show partial sums in the presentation tab but I get the above which is obviously not what I want.

Can you someone please help me out

thanks in advance

Dave

Re: Sub Totals ona pivot Table

Anonymous — Thu, 13 Oct 2016 14:52:30 GMT

Hi,

Can you share a screenshot of the presentation tab ?

Re: Sub Totals ona pivot Table

Thu, 13 Oct 2016 15:00:55 GMT

if you click on the table it will appear in another window

Re: Sub Totals ona pivot Table

vcanale — Thu, 13 Oct 2016 15:07:13 GMT

Hi,

Have you tried to enable the "show partial sums" on the "Customer" dimension instead of on the formulas?

Re: Sub Totals ona pivot Table

Thu, 13 Oct 2016 15:59:18 GMT

I enabled the show partial sums on all the dimensions I needed to total. I have tried enable it against the customer code and it doesn't have any total. I guess that's because it is a string not a number

Re: Sub Totals ona pivot Table

vcanale — Thu, 13 Oct 2016 16:09:10 GMT

Hi,
Try to add 'sales order quantity', 'sales order value' etc. as Expressions instead of Dimensions and try to enable the "show partial sums" on the last Dimension, eg: Item Description

Re: Sub Totals ona pivot Table

albertovarela — Thu, 13 Oct 2016 16:50:24 GMT

Go to properties:

Dimensions tab: Remove [Sales Order Quantity], [Sales Order Value], etc

Expression tab: create expressions like sum( [Sales Order Quantity]), etc..

Presentation tab: Select the Sales Order Num and enable Show Partial Sum. That will give you the subtotal by Customer Code

Re: Sub Totals ona pivot Table

Fri, 14 Oct 2016 12:43:42 GMT

I have done exactly what you said and I don't get any totals at all now. If I click on the last column I get a total for that one record only.

My expression code is

=if([LinkId] = 'SAL' and [Sales Order Status]='Authorised', [Sales Ordered Quantity])

Any idea how to supress a column in a pivot table. I have used the SAL Set analysis code but I don't want it displayed. I have used it in my expression but if I remove the dimension then I loose everything

Re: Sub Totals ona pivot Table

albertovarela — Fri, 14 Oct 2016 13:21:45 GMT

Not sure what are you trying to accomplish with the expression but try using an aggregation like sum ... see the example below

=if([LinkId] = 'SAL' and [Sales Order Status]='Authorised',sum ([Sales Ordered Quantity]))

Re: Sub Totals ona pivot Table

Tue, 18 Oct 2016 12:10:46 GMT

Hi Alberto

Thanks for you reply.

I am trying to get some totals on a pivot table. I need to total sales ordered quantity by customer code.

I have added the expression as detailed above.

My problem is that when I go into the presentations I check the show partials sums box and this then automatically ticks the expression I created as you suggested but it doesn't give any totals.

Any ideas?

Re: Sub Totals ona pivot Table

Anil_Babu_Samineni — Tue, 18 Oct 2016 12:17:42 GMT

Dave, Is there any chance to post the application(I mean QVW - Which you work around)

Re: Sub Totals ona pivot Table

Tue, 18 Oct 2016 12:27:57 GMT

how do I do that?

Please excuse my ignorance

Re: Sub Totals ona pivot Table

Anil_Babu_Samineni — Tue, 18 Oct 2016 12:57:54 GMT

Uploading a Sample

Re: Sub Totals ona pivot Table

Tue, 18 Oct 2016 13:06:42 GMT

I can't do that as it's company sensitive information on other tabs

Re: Sub Totals ona pivot Table

jyothish8807 — Tue, 18 Oct 2016 13:16:46 GMT

Hi Dave,

Try using advanced aggregated expression:

sum(aggr(sum({<[LinkId] = {'SAL'},[Sales Order Status]={'Authorised'}>}[Sales Ordered Quantity]),Dim1,Dim2,Dim3...))

Regards

Re: Sub Totals ona pivot Table

vcanale — Tue, 18 Oct 2016 13:17:09 GMT

Hi,
Better to have the screenshots of Dimensions and Expressions tabs (Chart Properties)