Totals within Pivot Table

[sunny_talwar]

** Update: Attached the sample QlikView file **

Hey Guys,

I have three Dimensions (Category, Item and Supplier). Each category can have multiple items in it. and I am trying to get here

Item	Sup1	Sup2	Sup3	Sup4	Total
Item1	1	1	1	1	4
Item2	1	1	1	1	4
Item3	1	1	1	1	4
Item4	1	1	1	1	4
Item5	1	1	1	1	4
Total	5	5	5	5	20

But Right now I am getting this using the following expression: =Count(DISTINCT TOTAL <Item, Category> Item)

Item	Sup1	Sup2	Sup3	Sup4	Total
Item1	1	1	1	1	1
Item2	1	1	1	1	1
Item3	1	1	1	1	1
Item4	1	1	1	1	1
Item5	1	1	1	1	1
Total	5	5	5	5	5

How do I make it so that it shows the total at both the ends?

Any help would be greatly appreciated.

Best,

S

[sunny_talwar]

I don't see any changes except that the Show Partial Sum for Supplier has been disabled.

Best,

S

[Anonymous]

added aggr expression . hopefully it will work now.

[sunny_talwar]

Hey Vinay,

Not what I want. I want this output:

Item	Sup1	Sup2	Sup3	Sup4	Total
Item1	1	1	1	1	4
Item2	1	1	1	1	4
Item3	1	1	1	1	4
Item4	1	1	1	1	4
Item5	1	1	1	1	4
Total	5	5	5	5	20

and Currently your aggregated formula is getting the following result:

Item	Sup1	Sup2	Sup3	Sup4	Total
Item1	-	1	1	-	2
Item2	1	1	1	1	4
Item3	-	1	1	1	3
Item4	1	1	1	1	4
Item5	1	-	-	1	2
Total	3	4	4	4	15

Best,

S

[Anonymous]

but there are holes in your data for supplier 1 you dont have Item 1 and item 3. please confirm

[Anonymous]

aggregate function is working but the problem is there is no data Buddy.

[sunny_talwar]

You are right Vinay, but within the Count I am using Total. I would expect the Total to work within the aggregate function, unless it doesn't. Because without the aggregate function, Total is working and showing 1 without any issues. Its just that as I use the aggregate, the 1's become 0 and sum changes.

Best,

S

[sunny_talwar]

The formula you used: sum(aggr(Count(DISTINCT TOTAL <Item, Category> Item),Category,Supplier,Item)) (This has TOTAL in it also

Output

and my formula: Count(DISTINCT TOTAL <Item, Category> Item)

Output

Best,

S

[sunny_talwar]

Trying my luck once again.

Please let me know if somebody has a solution for this.

Best,

S