topic Re: Pivot Table vertical and horizontal summing in QlikView

Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 09:31:02 GMT

Hi there

I have a problem with the calculation in a pivot table. I need to sum a column (MG Value) vertically, but instead it’s taking into consideration the other dimensions. In my example here “% of Total MG Value” should sum up to 100% but instead it’s see’s two dimensions and dividing this between the two (49.73% + 50.27% = 100%). Where both of them should have been 100% on their own.

Im attaching a reduced data model for you.

Re: Pivot Table vertical and horizontal summing

kristoferahlin — Wed, 19 Nov 2014 09:35:13 GMT

Hi,

Please see attached file. (You need to include also period in the TOTAL experssion)

Cheers,

Kristofer

Re: Pivot Table vertical and horizontal summing

tresB — Wed, 19 Nov 2014 09:35:55 GMT

A small correction in the expression would help:

sum([Total Portfolio Value]) / sum(TOTAL <MemberGroupName, PortfolioPeriod> [Total Portfolio Value])

Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 09:50:14 GMT

That is so awesome. I cant why i never tried that. Silly mistake.

But thanks a lot Kristofer. You're the best !!!

Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 09:52:25 GMT

Thanks to you too Tresesco. That solved my problem. It means I can go home now (after a few days).

Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 10:50:52 GMT

Just a follow-up question please? How do I get the other dimension (% of Total Fund Value) to add up to 100% at the bottom, instead of sharing it amongst the PortfolioYear dimensions.

Re: Pivot Table vertical and horizontal summing

tresB — Wed, 19 Nov 2014 10:55:44 GMT

sum([Total Portfolio Value]) / sum(TOTAL <FundName, PortfolioYear> [Total Portfolio Value])

Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 10:58:19 GMT

I've tried that. But it doesnt add up to 100% at the bottom.

Re: Re: Pivot Table vertical and horizontal summing

tresB — Wed, 19 Nov 2014 11:00:07 GMT

It does with me. PFA

Re: Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 11:12:58 GMT

It does add up to 100 if you only select 1 PortfolioYear. But as soon as you select more than 1, it does not.

Re: Pivot Table vertical and horizontal summing

tresB — Wed, 19 Nov 2014 11:20:54 GMT

You have PortfolioPeriod in dimension rather than PortfolioYear, in that case, try :

sum([Total Portfolio Value]) / sum(TOTAL <FundName, PortfolioPeriod > [Total Portfolio Value])

Re: Pivot Table vertical and horizontal summing

Wed, 19 Nov 2014 11:26:16 GMT

And yet again, it solves my problem. Im sorry, that was a rookie mistake. .

But thanks for you help !!!