Sum / Total Sum in Straight Table

[#1]

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

[MK_QSL]

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

Replace with appropriate field name...

[#3]

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.

[MK_QSL]

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

[#5]

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

[#6 · Accepted solution]

Hi Dev

Try this.

Column(1)

/

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

This is static, but will give you expected result.

[#7]

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 ?

[#8]

Any help please?

[#9]

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.