topic Re: particular cell summation in QlikView

particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 08:44:11 GMT

Hi,

The below data is shown as:

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

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

Re: particular cell summation

alexandros17 — Wed, 15 Jul 2015 08:48:36 GMT

This is an example ....

Re: particular cell summation

MK_QSL — Wed, 15 Jul 2015 08:50:01 GMT

Create a Pivot Table

Dimension

[Asset Class]

Position

Expression

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

Re: particular cell summation

qlikmsg4u — Wed, 15 Jul 2015 08:50:07 GMT

Hi Srujana,

See the attached example.

Create Pivot table

Dimension : [Asset Class]

Expression : Sum([Stress Test Values])

Re: particular cell summation

alex_millan — Wed, 15 Jul 2015 08:50:40 GMT

Hi,

you can set a pivot table:

- Asset class as Dimension

- Sum(Stress_test_values) As Expression

HTH,

Regards

Re: particular cell summation

sasiparupudi1 — Wed, 15 Jul 2015 08:56:48 GMT

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])

Re: particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 09:05:04 GMT

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	Position	Stress Test Values
Equity	-1298	position1	-264
Equity		position2	-912
Equity		position3	-122
Derivatives	-2105	position4	-960
Derivatives		position5	-509
Derivatives		position6	-636
Fixed Income	-122	position7	-84
Fixed Income		position8	-15
Fixed Income		position9	-23
Forex	-1843	position10	-857
Forex		position11	-287
Forex		position12	-699
Forex	-1276	position13	-450
Commodities		position14	-684
Commodities		position15	-142
Commodities	-1540	position16	-700
Equity		position17	-770
Equity		position18	-70
Equity	-1708	position19	-413
Derivatives		position20	-557
Derivatives		position21	-738
Derivatives	-2136	position22	-888
Fixed Income		position23	-290
Fixed Income		position24	-958
Fixed Income	-1890	position25	-890
Forex		position26	-457
Forex		position27	-543
Forex	-1311	position28	-908
Forex		position29	-148
Commodities		position30	-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

Re: particular cell summation

MK_QSL — Wed, 15 Jul 2015 09:10:20 GMT

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

Re: particular cell summation

alex_millan — Wed, 15 Jul 2015 09:15:58 GMT

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

Re: particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 09:43:17 GMT

please find the attached excel.

Re: particular cell summation

qlikmsg4u — Wed, 15 Jul 2015 09:50:07 GMT

Is this you want...please tell us the requires output

Re: particular cell summation

alex_millan — Wed, 15 Jul 2015 09:54:11 GMT

Hi again,

If you can switch the order of some of the columns, you can set the dimensions on the left and the calculations on the right:

As Dimensions:

- Portfolio

- [Asset class]

- Position

As expressions.

Sum x portfolio = Sum(TOTAL <Portfolio> [Stress Test Values])

Sum x asset class = Sum(TOTAL <[Asset class]>[Stress Test Values])

Sum x position = Sum([Stress Test Values])

HTH

Regards,

Re: particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 09:56:05 GMT

Hi,

I should get fields in a pivot table as Asset class, Position thru these two fields I need to calculate Column L from Column N.

Re: particular cell summation

antoniotiman — Wed, 15 Jul 2015 10:07:52 GMT

Hi this is what You want ?

See Attachment

Regards

Re: particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 10:16:14 GMT

Hi AntonioTiman,

Yes, but I have so many positions and Asset classes, I cant mention all the positions in that expression. Is there any other way to do it ?

any way thank you for your help.

regards

Srujana

Re: particular cell summation

Kushal_Chawda — Wed, 15 Jul 2015 10:17:29 GMT

Please see the attached.

This is what you are looking for

Re: particular cell summation

antoniotiman — Wed, 15 Jul 2015 10:19:56 GMT

Change expression to

Sum({<[Asset Class]={'Equity'}>} [Stress Test Values3])

Re: particular cell summation

srujanaponnuru — Wed, 15 Jul 2015 10:26:34 GMT

Hi Kush,

thanks a lot for your help. It worked.

Regards

Srujana

Re: particular cell summation

Kushal_Chawda — Wed, 15 Jul 2015 10:27:06 GMT

srujanaponnuru‌ have you seen the application which I have attached