topic Re: Totals within Pivot Table in QlikView

Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 17:36:06 GMT

** 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,

Re: Totals within Pivot Table

Colin-Albert — Fri, 09 Jan 2015 17:52:55 GMT

On the Chart Properties Presentation Tab, click on "Partial Sums" for the field at the level beneath the one you want the total to show against.

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 17:55:12 GMT

Hi Colin,

Thanks for your response. I see the totals (already have the selected). The problem is that One of the totals is not what I want it to be (Last Column on the above attached table).

Best,

Re: Totals within Pivot Table

Colin-Albert — Fri, 09 Jan 2015 18:01:35 GMT

The last column I can see is Sup4, both screenshots are the same in your post.

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 18:02:58 GMT

There is scroll bar at the bottom of my initial post, if you scroll to the right, you will see Total there.

Best,

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 18:57:29 GMT

can you please post the sample qvw file with explanation. i dont see category dimension and what you are trying to acheive. Please explain clearly.

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 19:00:28 GMT

I don't have a sample qvw right now, but I can work on preparing one. Once I have that I will post it here.

Thanks for you help Vinay,

Best,

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 19:22:47 GMT

Attaching a sample QlikView file. Let me know if still anything is unclear.

Best,

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 19:28:22 GMT

is that you are expecting

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 19:29:04 GMT

is this you are expecting

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 19:30:54 GMT

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

Best,

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 19:36:51 GMT

added aggr expression . hopefully it will work now.

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 19:49:53 GMT

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,

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 19:52:16 GMT

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

Re: Totals within Pivot Table

Anonymous — Fri, 09 Jan 2015 19:53:06 GMT

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

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 19:56:55 GMT

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,

Re: Totals within Pivot Table

sunny_talwar — Fri, 09 Jan 2015 20:01:27 GMT

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,

Re: Totals within Pivot Table

sunny_talwar — Mon, 12 Jan 2015 13:09:41 GMT

Trying my luck once again.

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

Best,