topic Re: Total in Pivot table in QlikView

Total in Pivot table

upaliwije — Thu, 17 Nov 2016 16:01:59 GMT

DESCRIPTION	UNIT_NOS	UNIT_COST	Total Cost
8 Port Switches	65	7,000	455,000
16 Port Switches (Cat6)	29	25,000	725,000
24 Port Switches (Cat6)	16	35,000	560,000
AD Servers	4	170,000	680,000
Total	114	237,000	27,018,000

Correct Total			2,420,000

In my above Pivot table when I apply partial sum, above Total 27,018,000 apper. But the actual Total is 2,420,000/- This may be due to my set expression for Total Cost is Unit_nos* Unit_Cost (114*237,000=27,018,000). Is there any method to show my correct total in the Total Row. If so pls educate me.

Re: Total in Pivot table

sunny_talwar — Thu, 17 Nov 2016 16:23:12 GMT

Try this:

Sum(Aggr(UNIT_NOS * UNIT_COST, DESCRIPTION))

Re: Total in Pivot table

upaliwije — Thu, 17 Nov 2016 16:27:40 GMT

Then I get following table

DESCRIPTION	UNIT_NOS	UNIT_COST	Total Cost
8 Port Switches	65	7,000	0
16 Port Switches (Cat6)	29	25,000	0
24 Port Switches (Cat6)	16	35,000	35,000
AD Servers	4	170,000	0
Total	114	237,000	35,000

Re: Total in Pivot table

sunny_talwar — Thu, 17 Nov 2016 16:29:20 GMT

Well I mean for you to replace UNIT_NOS and UNIT_COST with these actual expressions

Sum(Aggr(UNIT_NOSExpression * UNIT_COSTExpression, DESCRIPTION))

Re: Total in Pivot table

upaliwije — Thu, 17 Nov 2016 16:38:04 GMT

Sorry I dont get what you say

My 3 expression are as follows

SUM(UNIT)

SUM(UNIT_COST)

Sum(Aggr(UNIT * UNIT_COST, DESCRIPTION)) - as suggested by you

Re: Total in Pivot table

sunny_talwar — Thu, 17 Nov 2016 17:16:51 GMT

This is what I meant

Sum(Aggr(SUM(UNIT) * SUM(UNIT_COST), DESCRIPTION))

Re: Total in Pivot table

hectorvega — Thu, 17 Nov 2016 23:37:30 GMT

Upali, did you use an if with a Dimensionality()=0 to get the correct sums on the total?

Re: Total in Pivot table

upaliwije — Fri, 18 Nov 2016 03:22:08 GMT

Thanks a lot