topic Re: Sum in Table in QlikView

Sum / Total Sum in Straight Table

Tue, 12 Nov 2013 15:47:46 GMT

Hello Every one.

I am trying to get total of sum of values in chart. I have calculated dimension and have only two value from dimension displayed from multiple values..

Required to divide

240,015 / 480,833

236,818 / 480,833

With total I am getting 1,283,562

I want to get % by following formula,

Column(1)/ Sum of Column (1)

Currently

Sum({<Year={$(v_PY2)} >} Sales)

Sum({<Year={$(v_PY2)} >} Total Sales)

Please see table below

This is Calculated Dimension	FY 2011	%	Should be	Value Calculated with Total in expression
High Grade	244,015	19%	51%	1283562	19%
High Yield	236,818	18%	49%	1283562	18%
Total Volume	480,833	37%	100%	1283562	37%

Here is IF statement, calculated dimension.

if( ([ID 5]='High Grade US' or match([ID 7],'Financial Institutions','Industrials')>0)

, 'High Grade',

If([ID 5]='High Yield US' or [ID 6]='Loan Syndicate US' or [ID 7]='High Yield US',

'High Yield', NULL()

)

Any help please ?

Thank you in advance..

Message was edited by: Dev Pat Added Formula

Re: Sum in Table

MK_QSL — Tue, 12 Nov 2013 16:11:56 GMT

SUM(FY)/SUM({<Your Calculated Dimension = {'High Grade','High Yield'}>}TOTAL FY)

Replace with appropriate field name...

Re: Sum in Table

Tue, 12 Nov 2013 16:19:39 GMT

Hi Manish

Thank you,

How can I put Calculated dimension in Set analysis, it is if statement in Calculated dimension. I can not put that in set analysis.

Re: Sum in Table

MK_QSL — Tue, 12 Nov 2013 16:25:25 GMT

Ohhh.. Sorry... Can you upload an example file?

Re: Sum in Table

Tue, 12 Nov 2013 16:25:58 GMT

Hi QlikviewFan,

Since you are using a calculated dimension instead of a dimension it will be possible that it will be more values for the calculated dimensions than "High Grade" or "High Yield", therefore when you calculate the TOTAL in the sum you are adding the value for "High Grade" and for "High Yield" but also the rest of the values, thus the difference (the TOTAL is higher than u expected).

Hope it helps.

Regards

Re: Sum in Table

Tue, 12 Nov 2013 16:40:29 GMT

Hi Dev

Try this.

Column(1)

RangeSum(Above(Column(1), Column(1), Below (Column(1))))

This is static, but will give you expected result.

Re: Sum in Table

Tue, 12 Nov 2013 17:41:29 GMT

Hi Jaime/ Manish,

Thanks,

I am aware of it, and you are correct there are many values in the dimension field. Is there any work around to get it?

Here is my If Statement

Here is IF statement, calculated dimension.

if( ([ID 5]='High Grade US' or match([ID 7],'Financial Institutions','Industrials')>0)

, 'High Grade',

If([ID 5]='High Yield US' or [ID 6]='Loan Syndicate US' or [ID 7]='High Yield US',

'High Yield', NULL()

)

Any help please ?

Re: Sum / Total Sum in Straight Table

Wed, 13 Nov 2013 15:22:44 GMT

Any help please?

Re: Sum / Total Sum in Straight Table

Thu, 14 Nov 2013 08:41:09 GMT

Hi,

This is not very elegant, although it works for me,

Try the following formula in the expression:

=If([ID 5]='High Yield US' or [ID 6]='Loan Syndicate US' or [ID 7]='High Yield US',

sum(Sales) / (RangeSum(Above(Column(1),0))+ RangeSum(Above(Column(1),1))),

sum(Sales) / (RangeSum(Above(Column(1),0))+RangeSum(Above(Column(1),-1))))

Regards.