topic Getting a Total in a Pivot table in QlikView

Getting a Total in a Pivot table

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

We have several Salespeople who get paid by the unit sold, but it is a different amount for different items; I am trying to calculate their pay.

I have a Pivot Table with the following Dimensions;

Customer.Sales Rep Full Name (with Partial Sum checked)

Year

Week (with Partial Sum checked)

Transactions.Item Full Name
Transactions.Descriptions

And the following Expressions;

Amount of Transactions – count(Distinct [Transactions.Ref Number])

# of units sold - sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity))

Commissions – if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity, Transactions.Quantity)) *.25)

This works accurately on each line but I can’t get a total for the commissions unless I select only 1 item, I do get an accurate total for Transactions and Units.

Thank You

Re: Getting a Total in a Pivot table

neelamsaroha157 — Tue, 07 Aug 2018 20:35:36 GMT

May be you can try the aggregation function -

Commissions – Aggr(if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.25), your dimensions)

Re: Getting a Total in a Pivot table

Anonymous — Wed, 08 Aug 2018 15:44:03 GMT

I'm sorry, I'm new at this but I don't understand what you mean by 'your dimensions' (at the end)

Re: Getting a Total in a Pivot table

neelamsaroha157 — Wed, 08 Aug 2018 17:44:08 GMT

It means all the dimensions that you mentioned above -

Customer.Sales Rep Full Name (with Partial Sum checked)

YearWeek (with Partial Sum checked)

Transactions.Item Full Name
Transactions.Descriptions

Aggr(if ([Transactions.Item Full Name]=1 or [Transactions.Item Full Name]=2,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.15),
if ([Transactions.Item Full Name]=3,
(sum(if([Transactions.Txn Type]='Credit Memo', -Transactions.Quantity,Transactions.Quantity)) *.25), [Customer.Sales Rep Full Name], [YearWeek] , [Transactions.Item Full Name], [Transactions.Descriptions]

)