Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
srujanaponnuru
New Contributor III

particular cell summation

Hi,

The below data is shown as:

                                                                                       

Asset
  Class
PositionStress Test
  Values
Equityposition1-699
Equityposition2-118
Equityposition3-972
Derivativesposition4-870
Derivativesposition5-63
Derivativesposition6-884
Fixed Incomeposition7-98
Fixed Incomeposition8-73
Fixed Incomeposition9-242
Forexposition10-398
Forexposition11-50
Forexposition12-659
Forexposition13-474
Commoditiesposition14-190
Commoditiesposition15-632
Commoditiesposition16-533

I want a pivot table to calculate the sum of stress test values only for equity type , derivatives etc separately.

example:  for equity I want the summation of position 1 + position 2 + Positiion 3 (I,e  = (-699+(-118)+-(-972))

Please help me out.

REgards

Srujana

1 Solution

Accepted Solutions
Highlighted

Re: particular cell summation

Please see the attached.

This is what you are looking for

View solution in original post

18 Replies
Highlighted

Re: particular cell summation

This is an example ....

Highlighted
MVP
MVP

Re: particular cell summation

Create a Pivot Table

Dimension

[Asset Class]

Position

Expression

IF([Asset Class] = 'Equity',SUM(TOTAL <[Asset Class]> [Stress Test Values]), SUM([Stress Test Values]))

Highlighted
qlikmsg4u
Valued Contributor

Re: particular cell summation

Hi Srujana,

See the attached example.

Create Pivot table

Dimension : [Asset Class]

Expression : Sum([Stress Test  Values])

Highlighted
alex_millan
Contributor III

Re: particular cell summation

Hi,

you can set a pivot table:

- Asset class as Dimension

- Sum(Stress_test_values) As Expression

HTH,

Regards

Highlighted
sasiparupudi1
Honored Contributor III

Re: particular cell summation

Table1:

load * inline

[

Asset Class,Position,Stress Test Values

Equity,position1,-699

Equity,position2,-118

Equity,position3,-972

Derivatives,position4,-870

Derivatives,position5,-63

Derivatives,position6,-884

Fixed Income,position7,-98

Fixed Income,position8,-73

Fixed Income,position9,-242

Forex,position10,-398

Forex,position11,-50

Forex,position12,-659

Forex,position13,-474

Commodities,position14,-190

Commodities,position15,-632

Commodities,position16,-533

];

Use a chart object and select pivot table option

select Asset Class as dimension

create an expression and enter the sum([Stress Test Values])

Highlighted
srujanaponnuru
New Contributor III

Re: particular cell summation

Thanks everyone. Actually all the answers are correct but I haven't given the whole data.

I have the data as

                                                                                                                                                                         

Asset
  Class
Stress Test
  Values
PositionStress Test
  Values
Equity-1298position1-264
Equityposition2-912
Equityposition3-122
Derivatives-2105position4-960
Derivativesposition5-509
Derivativesposition6-636
Fixed Income-122position7-84
Fixed Incomeposition8-15
Fixed Incomeposition9-23
Forex-1843position10-857
Forexposition11-287
Forexposition12-699
Forex-1276position13-450
Commoditiesposition14-684
Commoditiesposition15-142
Commodities-1540position16-700
Equityposition17-770
Equityposition18-70
Equity-1708position19-413
Derivativesposition20-557
Derivativesposition21-738
Derivatives-2136position22-888
Fixed Incomeposition23-290
Fixed Incomeposition24-958
Fixed Income-1890position25-890
Forexposition26-457
Forexposition27-543
Forex-1311position28-908
Forexposition29-148
Commoditiesposition30-255

I want the summation of stress test values of equity only for position 1, 2, and 3 and then again if the equity is repeated I want the summation of different positions.

Thank you for helping.

Regards

Srujana

Highlighted
MVP
MVP

Re: particular cell summation

Very confusing description. Can you please upload an excel file with required output?

Highlighted
alex_millan
Contributor III

Re: particular cell summation

Hi,

you can get the sum of stress value x class with the following expression:

     Sum(Total<[Asset class]> Aggr(Sum([Stress Test Values]),Position))

then you can do another expression to get the sum of stress value x position:

     Sum([Stress Test Values])

Regards

Highlighted
srujanaponnuru
New Contributor III

Re: particular cell summation

please find the attached excel.